Sunday, December 28, 2008

How to create triggers – Oracle?

Any updates made to table, specific to data but not schema can be traced using triggers.
Data updates could be like inserting new row or updating an existing row in a data base table.
So this updates are categorized into Insert and Update operations.
When creating trigger on a table parameters that are to be considered are

1) Trigger name to identify uniquely among the existing triggers.
2) Trigger event that has to be raised before or after of a specific action.
3) Event action (Insert / Update).
4) Table name on which we are placing trigger.
5) Is Insert/Update for each row? (This is because there could be bulk inserts on a table, where multiple rows are inserted at once
Bulk insert in Prog. findings).

Syntax :-
Create or Replace trigger [TRIGGER_NAME]
[AFTER/BEFORE] [INSERT/UPDATE] on [TABLE NAME]
[optional : FOR EACH ROW]
declare
variable declaration
begin
trigger body
end;

Example :-
I will place a trigger on a table of insert type.
In this trigger i need to generate new id for each record that is been inserted into this table.

Actions :-

1) We need to create 3 triggers of Insert type to handle this.
a) After Insert for each row in this table to store the relevant row id in a package.
b) Before Insert to clear the package content.
c) After Insert to get row ids from the package and update the id of the row.

Table
create table test
(
id number,
name varchar2(10)
);

Package to store row id’s
CREATE OR REPLACE PACKAGE pkg_test
AS
TYPE ridArray IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
newRows ridArray;
empty ridArray;
END;
/

Trigger after insert for each row in Test table

CREATE OR REPLACE TRIGGER TRG_TEST_AIFR
AFTER INSERT ON TEST FOR EACH ROW --after insert, for each row
BEGIN
--fills the package with inserted row id(s)
Pkg_TEST.newRows( Pkg_TEST.newRows.COUNT+1 ) := :NEW.ROWID;
END;
/


Trigger after insert in Test table

CREATE OR REPLACE TRIGGER TRG_TEST_AI
AFTER INSERT ON TEST --after insert trigger
DECLARE
tempID NUMBER;
BEGIN

FOR i IN 1.. Pkg_TEST.NEWROWS.COUNT
LOOP
select decode(max(id),null,1,max(id)+1) into tempID from test;
UPDATE TEST SET ID =tempID WHERE ROWID = Pkg_TEST.newRows(i);
END LOOP;
END;
/



Trigger before insert in Test table


CREATE OR REPLACE TRIGGER TRG_TEST_BI
BEFORE INSERT ON TEST --before insert trigger
BEGIN
--clears the package
Pkg_Test.newRows := Pkg_Test.empty;
END;
/
After running the below insert query
INSERT INTO TEST (name) VALUES('Srinivas');
ID is automatically generated from trigger, which should be '1' as this is the first record.


No comments: