skip to content »

Oracle triggers if inserting updating

Now before updating ’empid’ column in ’emp’ table see the ’emp’ and ’emp_backup’ table records.Vishwanath Dalvi is a gifted engineer and tech enthusiast. When not hacking around or supporting the open source community, he is trying to overcome his phobia of dogs.

oracle triggers if inserting updating-42oracle triggers if inserting updating-31oracle triggers if inserting updating-23oracle triggers if inserting updating-29

If one or more ON INSERT, ON DELETE or ON UPDATE triggers are defined on a view, then it is not an error to execute an INSERT, DELETE or UPDATE statement on the view, respectively.A database trigger is a stored procedure that automatically executes whenever an event occurs. Oracle initiates an ‘AFTER INSERT’ trigger after an insert event has been occurred and an ‘AFTER UPDATE’ trigger after an update event has been occurred.CREATE or REPLACE TRIGGER emp_after_insert AFTER INSERT ON emp FOR EACH ROW DECLARE BEGIN insert into emp_backup values (:new.empid, :new.fname, :new.lname); DBMS_OUTPUT.PUT_LINE('Record successfully inserted into emp_backup table'); END; Second Problem: We want o update a record in the ’emp_backup’ table if a corresponding record is updated in the ’emp’ table.As we’ve already created both the tables in above example, we will directly write a trigger for ‘AFTER UPDATE’.A trigger normally exists in the same database as the table named after the "ON" keyword in the CREATE TRIGGER statement.

Except, it is possible to create a TEMP TRIGGER on a table in another database.

Note how the values assigned to the view are made available as field in the special "NEW" table within the trigger body.

If a BEFORE UPDATE or BEFORE DELETE trigger modifies or deletes a row that was to have been updated or deleted, then the result of the subsequent update or delete operation is undefined.

Triggers are automatically dropped when the table that they are associated with (the table-name table) is dropped.

However if the trigger actions reference other tables, the trigger is not dropped or modified if those other tables are dropped or modified.

Hence explicitly specifying FOR EACH ROW is optional.