Recently I have come a cross a task of writing a DB Trigger. My requirement was to save some intermediate data in to a new table. Instead of changing the code for saving those data to a new table, I used the method of recording those data using a db trigger to the relevant table. That was again a new experience for me. So I am sharing the things I have learnt with you all !!:)
Do you know about db triggers ? What is a db trigger ?
Do you know about db triggers ? What is a db trigger ?
I am using one of the available definitions in the web for define the db trigger.
"Oracle allows you to define procedures that are implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against the associated table. These procedures are called database triggers." Hope you can get the idea.
When I surf the web for more information I have found out more facts about triggers.
What are the parts of a typical db trigger ?
Mainly there are 3 parts of the db trigger.
1. Triggering Statement
2. Trigger Restriction
3. Trigger Action
What is Triggering Statement ?
A triggering statement is the SQL statement that causes a trigger to be fired. This Triggering Statement can be an INSERT, UPDATE or DELETE statement on a table.
A trigger restriction specifies a Boolean expression that must be TRUE for the trigger to fire.
There are 2 types of Triggers. So I have to find out which one suites for me in this case. I searched for them.
When I surf the web for more information I have found out more facts about triggers.
What are the parts of a typical db trigger ?
Mainly there are 3 parts of the db trigger.
1. Triggering Statement
2. Trigger Restriction
3. Trigger Action
What is Triggering Statement ?
A triggering statement is the SQL statement that causes a trigger to be fired. This Triggering Statement can be an INSERT, UPDATE or DELETE statement on a table.
What is Trigger Restriction ?
What is Trigger Action?
A trigger action is the procedure (PL/SQL) that contains the PL/SQL code to be executed when a triggering statement is issued.
What are the available types of Triggers ?
There are 2 types of Triggers. So I have to find out which one suites for me in this case. I searched for them.
There are two types of triggers as follows.
1. Row Triggers
2. Statement Triggers
What is Row Level Trigger?
A row trigger is fired each time the table is affected by the triggering statement.
When to use Row Level Triggers?
A row trigger is useful if the code in the trigger action depends on data provided by the rows that are affected.
2. Statement Triggers
What is Row Level Trigger?
A row trigger is fired each time the table is affected by the triggering statement.
When to use Row Level Triggers?
A row trigger is useful if the code in the trigger action depends on data provided by the rows that are affected.
What is Statement Level Trigger?
A statement trigger is fired once on behalf of the triggering statement. There is no concern about the number of rows in the table that the triggering statement affects
When to use Statement Level Triggers?
When to use Statement Level Triggers?
Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected.
According to my requirement. I had to use the Row Level Trigger. Because I wanted to apply the code inside the triggering action to be executed for each row.
Next was to choose one from BEFORE or AFTER Triggers. Because when defining triggers we can specify the time, that is when to fire the trigger action.
What is this BEFORE Trigger?
According to my requirement. I had to use the Row Level Trigger. Because I wanted to apply the code inside the triggering action to be executed for each row.
Next was to choose one from BEFORE or AFTER Triggers. Because when defining triggers we can specify the time, that is when to fire the trigger action.
What is this BEFORE Trigger?
As it name implies, Before trigger executes the trigger action before the triggering statement. Actually this types of triggers helpful to derive specific column values before executing the triggering statement.
What is AFTER Trigger?
What is AFTER Trigger?
After trigger executes the trigger action after the triggering statement.
According to my requirement I have to choose the After Trigger. So I have designed the skeleton of my trigger as follows.
CREATE OR REPLACE
TRIGGER MY_FIRST_DB_TRIGGER AFTER
INSERT OR
UPDATE OR DELETE ON TRIGGER_ON_TABLE
FOR EACH ROW
DECLARE
-- define parameters here
;
;
BEGIN
IF inserting THEN
-- logic that should execute for the inserting event
--INSERT INTO NEW_TABLE (column_1, column_2)
--VALUES (:NEW.ID, :NEW.NAME );
--INSERT INTO NEW_TABLE (column_1, column_2)
--VALUES (:NEW.ID, :NEW.NAME );
ELSIF updating THEN
-- logic that should execute for the updating event
ELSE
-- logic that should execute for the deleting event
END IF;
END MY_FIRST_DB_TRIGGER;
How to access the column values in Row Triggers?
Within a trigger body of a row trigger, the PL/SQL code have access to the old and new column values of the current row affected by the triggering statement. So that you can access the new value for the row is being created as :NEW and the already available column value as :OLD.
Actually in this case,
* If the trigger has fired by an INSERT statement has meaningful access to new column values only and the old values are null. Because there is only the :NEW value for the row being created.
* If the trigger has fired by an UPDATE statement has access to both old and new column values.
* If the trigger has fired by a DELETE statement has access to :OLD column values only.
Actually in this case,
* If the trigger has fired by an INSERT statement has meaningful access to new column values only and the old values are null. Because there is only the :NEW value for the row being created.
* If the trigger has fired by an UPDATE statement has access to both old and new column values.
* If the trigger has fired by a DELETE statement has access to :OLD column values only.
Cheers
"do good and good will come to you !!"
No comments:
Post a Comment