Database Trigger
A database trigger is a block of code that is automatically executed in response to certain events. Triggers are executed implicitly whenever the triggering event happens.
The triggering event is either a INSERT, DELETE, or UPDATE command.
The timing can be either BEFORE or AFTER, INSTEAD OF trigger
The trigger can be either row-level or statement-level, where the former fires once for each row affected by the triggering statement and the latter fires once for the whole statement.
Example
Example of creating a trigger based on the following two tables:
CREATE TABLE T1 (a INTEGER);
CREATE TABLE T2 (b INTEGER);
We will create a trigger that may insert a tuple into T2 when a tuple is inserted into T1. The trigger checks if the inserted row in T1 is has a value less than 5 only then a tuple is inserted in T2.
CREATE TRIGGER tr1
AFTER INSERT ON T1
REFERENCING NEW AS newRow
FOR EACH ROW
WHEN (newRow.a <= 5)
BEGIN
INSERT INTO T2 VALUES(:newRow.a);
END tr1;
.
run;
Displaying Trigger Errors
If we get a message Warning: Trigger created with compilation errors. you can check the error messages with: show errors trigger
Viewing Defined Triggers
To view all the defined triggers, use:
select name_of_trigger from user_triggers;
For more details on a particular trigger:
select trigger_type, triggering_event, table_name, referencing_names, trigger_body
from user_triggers
where trigger_name = '<>';
Disabling Triggers
To disable or enable a trigger:
alter trigger <> {disableenable};
Mutating Table Errors
Permissions
For creating triggers you should have create trigger privilege :
Example
Grant create trigger to John; Sample table:create table t1 (
id int,
name varchar(10),
primary key(id)
);
Before insert trigger:
Create or replace
Trigger tr1
Before
Insert
On t1
For each row
Begin
Dbms_output.put_line(’before insert of ’ :new.name);
End;
We will insert some data to see if our triggers work or not:
insert into t1(id,name) values (1,’sam’);
Result:
before insert of sam
After insert trigger:
Create or replace
Trigger tr1
After
Insert
On t1
For each row
Begin
Dbms_output.put_line(’After insert of ’ :new.name);
End;
We will insert some data to see if our triggers work or not:
insert into t1(id,name) values (1,’sam’);Result:
After insert of sam
Before Update Statement Trigger:
create or replace
trigger tr1
before update
on t1
begin
dbms_output.put_line(’before updating some names(s)’);
end;
create or replace
trigger tr1
before update
on t1
for each row
begin
dbms_output.put_line(’before updating ’
(:old.name) ’ to ’
to_char(:new.name));
end;
IF statements
create or replace
trigger tr1
before insert or update or delete on t1
for each row
begin
if inserting then
dbms_output.put_line(’inserting : ’ :new.name);
elsif updating then
dbms_output.put_line(’updating : ’
:old.name ’ to ’ :new.name);
elsif deleting then
dbms_output.put_line(’deleting : ’ :old.name);
end if;
end;
We will insert some data to see if our triggers work or not:
insert into t1(id,name) values (1,’sam’);
Result:
inserting : sam
update t1 set name = ’g’ where name = ’a’;
updating: g to s
Working with Views
we will create a view (of t1 table):
CREATE OR REPLACE
VIEW t1_VIEW AS
SELECT NAME FROM t1;
Now, we know that updating (or inserting) into a view is kind of pointless; however, we can provide this functionality using a trigger!
Example:
CREATE OR REPLACE
TRIGGER t1_VIEW_INSERT
INSTEAD OF INSERT ON t1_VIEW
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE(’INSERTING: ’ :NEW.NAME);
-- we can also do
-- INSERT INTO t1(ID,NAME) VALUES (N,:NEW.NAME);
END;
When we do an insert statement on t1_VIEW:
INSERT INTO t1_VIEW(NAME) VALUES (’ poly’);
Which produces the result:
INSERTING: poly
The trigger will be fired when someone will try to insert a value into a VIEW.
QTP-7
61) D, 62) A, 63) D, 64) C, 65) A, 66) C, 67) A, 68) B, 69) D, 70) D
