Pl sql trigger inserting deleting updating


01-Jul-2020 15:57

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.CREATE or REPLACE TRIGGER emp_after_update AFTER UPDATE OF empid ON emp FOR EACH ROW DECLARE BEGIN update emp_backup set empid = :new.empid where empid = :old.empid; DBMS_OUTPUT.PUT_LINE('empid successfully updated into emp_backup table'); END; The above trigger named ’emp_after_update’ will be initiated whenever ’empid’ column in ’emp’ table gets updated.

They are also created when a SELECT statement that returns just one row is executed.

A cursor can hold more than one row, but can process only one row at a time.

The set of rows the cursor holds is called the set.

We can create a trigger to update the 'product_price_history' table when the price of the product is updated in the 'product' table.

1) Create the 'product' table and 'product_price_history' table Once the above update query is executed, the trigger fires and updates the 'product_price_history' table.A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table.A trigger is triggered automatically when an associated DML statement is executed.For example, a table and a trigger can have the same name (however, to avoid confusion, this is not recommended).