With the compound trigger, both the statement-level and row-level action can be put up in a single trigger.Plus there is an added advantage: it allows sharing of common state between all the trigger-points using variable.CASE WHEN INSERTING THEN -- Include any code specific for when the trigger is fired from an INSERT. WHEN UPDATING THEN -- Include any code specific for when the trigger is fired from an UPDATE. WHEN DELETING THEN -- Include any code specific for when the trigger is fired from a DELETE. DML triggers have four basic timing points for a single table. With the exception of Compound Triggers, the triggers for the individual timing points are self contained and can't automatically share state or variable information.The workaround for this is to use variables defined in packages to store information that must be in scope for all timing points.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.
FOR EACH ROW implies that the SQL statements specified in the trigger may be executed (depending on the WHEN clause) for each database row being inserted, updated or deleted by the statement causing the trigger to fire.By commenting, you are accepting the IBM commenting guidelines and the DISQUS terms of service.The CREATE TRIGGER statement has a lot of permutations, but the vast majority of the questions I'm asked relate to basic DML triggers.clause define the timing point for the trigger, which is explained below.
There are optional declaration and exception sections, like any other PL/SQL block, if required.A trigger may be specified to fire whenever a DELETE, INSERT, or UPDATE of a particular database table occurs, or whenever an UPDATE occurs on on one or more specified columns of a table.