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.
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.
Each trigger extends a collection defined in the package and stores a message with the trigger name and the current action it was triggered with.
In addition, the after statement trigger displays the contents of the collection and empties it.
to ensure that necessary data goes into the table, in some cases allowing the application layer to omit the information from their queries, and in some cases forcing that it be present.
The actions that I need to do on my -- Scenario 1 CREATE OR REPLACE TRIGGER my_trg BEFORE INSERT OR UPDATE ON my_table FOR EACH ROW BEGIN IF UPDATING THEN IF : NEW.my_col IS NULL THEN raise_application_error(...); END IF; IF : NEW.my_col2 IS NULL THEN : NEW.my_col2 := (...); END IF; END IF; IF INSERTING THEN IF : NEW.my_col3 IS NULL THEN SELECT my_seq.
In my experience, Developers/Business Analysts often tend to think in transaction workflows.
I have found this to be especially helpful when I or others have to troubleshoot the consequences of user data corruption as it relates to triggers being fired - the less triggers you have to try to piece together the logic for, the easier it is to see how data was manipulated. Obviously the separate triggers are more efficient because you're doing fewer logical checks, but you have a decent point on the benefit of having fewer triggers.
The following code builds a collection of 5 records, then uses that to drive bulk inserts, updates and deletes on the TRIGGER_TEST table.
The triggers from the previous section will reveal the timing points that are triggered.
The "dml-event" can be one or more of the following.