p28

/*
Triggers

A trigger is a little program that is
attached to a table.

Whenever somebody updates/deletes/inserts
into that table, the trigger program will
be executed. 

That program can do whatever you want.
Like... remember when the update happened.

*/

p29


-- A trigger example.

/* Whenever somebody deletes something from my table,
I just print a message. */


create or replace trigger jennifer
before delete on x 
for each row
begin
  dbms_output.put_line('Warning, somebody is trying to delete');
end;
/

p29b


-- This does NOT work!!!!
-- You cannot declare a trigger for truncate!

create or replace trigger jennifer
before truncate on x                  <<<< NO NO NO
for each row
begin
  dbms_output.put_line('Warning, somebody is trying to delete');
end;
/

If you do 

truncate table x

the delete trigger will NOT be activated!!!

p29c


-- This does NOT work!!!!

-- No trigger for SELECT

create or replace trigger jennifer
before select on x 
for each row
begin
  dbms_output.put_line('Warning, somebody is trying to see your secrets');
end;
/

p29d


-- So how do you stop deleting effectively?

CREATE OR REPLACE TRIGGER stop_delete
BEFORE DELETE ON X 
BEGIN
        RAISE_APPLICATION_ERROR(-20001, 'Delete operation not allowed!');
END;

-----------------------------------------
To detect which user executed a command:


select user from dual

p29e


-- Recursive calls from trigger:

create or replace trigger trident
after insert on x

begin
    dbms_output.put_line('Warning!');
    insert into x values(5,25,125);
end;
/ 

insert into x values(6, 36, 216)
            *

ERROR at line 1:
ORA-00036: maximum number of recursive SQL levels (50) exceeded
ORA-06512: at "GELLER.TRIDENT", line 3
ORA-04088: error during execution of trigger 'GELLER.TRIDENT'
ORA-06512: at "GELLER.TRIDENT", line 3
ORA-04088: error during execution of trigger 'GELLER.TRIDENT'
ORA-06512: at "GELLER.TRIDENT", line 3
ORA-04088: error during execution of trigger 'GELLER.TRIDENT'
ORA-06512: at "GELLER.TRIDENT", line 3
ORA-04088: error during execution of trigger 'GELLER.TRIDENT'
ORA-06512: at "GELLER.TRIDENT", line 3
ORA-04088: error during execution of trigger 'GELLER.TRIDENT'
ORA-06512: at "GELLER.TRIDENT", line 3
ORA-04088: error during execution of trigger 'GELLER.TRIDENT'
ORA-06512: at "GELLER.TRIDENT", line 3
ORA-04088: error during execution of trigger 'GELLER.TRIDENT'
ORA-06512: at "GELLER.TRIDENT", line 3
ORA-04088: error during execution of trigger 'GELLER.TRIDENT'
ORA-06512: at "GELLER.TRIDENT", line 3
ORA-04088: error during execution of trigger 'GELLER.TRIDENT'
ORA-06512: at "GELLER.TRIDENT", line 3
ORA-04088:

Note that the table X was not changed at all.


p29f

This has nothing to do with triggers.
But it is also useful.  Formatting output.

begin
   dbms_output.put_line('Hello' || chr(13) || chr(13) || chr(13) || 'World');  -- New lines
   dbms_output.put_line('Hello' || chr(9) || chr(9) || chr(9) || 'World');  -- Tabs
end;

Even nicer is this:

begin
   dbms_output.put_line(rpad('Hello' , 20) || rpad('World', 20));
   dbms_output.put_line(rpad('Hello you' , 20) || rpad('Beautiful World', 20)); 
end;
/

p30


-- This is an improved trigger.
-- Now I am assuming an update.
-- I am storing the value before update and after update in a 
-- table called track.  Loaded from track.sql.

-- For each row... means for each row that is affected by my
-- trigger statement.


create or replace trigger jennifer
before update on x 
for each row
begin
  dbms_output.put_line('Here I am before changing');
  dbms_output.put_line(:old.num || ' ' || :old.sqr || ' ' || :old.cube);
  dbms_output.put_line('Here I am after changing');
  dbms_output.put_line(:new.num || ' ' || :new.sqr || ' ' || :new.cube);

  insert into track values(:old.num, :old.sqr, :old.cube,
			   :new.num, :new.sqr, :new.cube);
end;
/


You can do "after update."

/*

Call this with an update statement such as:

update x
set num = 2 * num;

or

update x
set num = 5, sqr = 25, cube = 125
where num = 3;

*/

p31

-- This is a statement trigger.
-- As opposed to a row trigger.


create or replace trigger trident
before update on x 
-- for each row   -- It's done only ONCE for the whole table
begin
  dbms_output.put_line('');
  dbms_output.put_line(' **** Several lines are being changed');
end;
/

/*

Call this with an update statement such as:

update x
set num = 2 * num;

Now, just for fun:

update x
set num = num + 0;

Will this trigger, or not?


*/

p32

-- Now we specialize the trigger to a column.
-- Only that column starts the trigger.


-- :old and :new are just called prefixes.

create or replace trigger trident
before update of num               -- one column only!!
on x                     -- still table x
for each row
begin
  dbms_output.put_line('Here I am before changing');
  dbms_output.put_line(:old.num || ' ' || :old.sqr || ' ' || :old.cube);
  dbms_output.put_line('Here I am after changing');
  dbms_output.put_line(:new.num || ' ' || :new.sqr || ' ' || :new.cube);
end;
/

NOTE:  
Cannot do columns with INSERT trigger.
Cannot do columns with DELETE trigger.
Because they both don't make sense.
You can only insert or delete a whole row.


/*

Call this with an update statement such as:

(trigger effect) 

update x
set num = 2 * num;

or
(no effect)

update x
set sqr = 2 * sqr;

*/

p32b


/* extended version with two columns and
two possible events: update or delete
*/

create or replace trigger trident
before update or delete of num, sqr               
on x                     -- still table x
for each row
begin
  dbms_output.put_line('Here I am before changing');
  dbms_output.put_line(:old.num || ' ' || :old.sqr || ' ' || :old.cube);
  dbms_output.put_line('Here I am after changing');
  dbms_output.put_line(:new.num || ' ' || :new.sqr || ' ' || :new.cube);
end;
/

p33


--  Now we add an additional trigger condition for selecting rows
--  according to their content.
--  Secondly, the trigger needs to be loaded after the table, or
--  things don't work.
--  Thirdly, it took me a long time to get the trigger condition
--  to work.  I got it to work by doing what I thought was the
--  most unlogical thing possible.... Remove the : from the :old.
--  And look... it works...


create or replace trigger trident
before update
on x       
for each row
when (old.num > 3)  -- Trigger Condition
begin
  dbms_output.put_line('Here I am before changing');
  dbms_output.put_line(:old.num || ' ' || :old.sqr || ' ' || :old.cube);
  dbms_output.put_line('Here I am after changing');
  dbms_output.put_line(:new.num || ' ' || :new.sqr || ' ' || :new.cube);
end;
/

/*

Call this with an update statement such as:

(trigger effect) 

update x
set num = 2 * num;

or
(no effect)

update x
set sqr = 2 * sqr;

*/

p33b


-- Does NOT work!!!!

create or replace trigger trident
before update
on x       
for each row
when (num > 3)  -- Trigger Condition
begin
  dbms_output.put_line('Here I am before changing');
  dbms_output.put_line(:old.num || ' ' || :old.sqr || ' ' || :old.cube);
  dbms_output.put_line('Here I am after changing');
  dbms_output.put_line(:new.num || ' ' || :new.sqr || ' ' || :new.cube);
end;
/

p33c

How to see your own triggers.

select * from user_objects

Even better:

select * from user_triggers

--------------------

How many triggers can you put on one table?

I create three identical triggers (trident1, trident2, trident3).
They are all:

before update on x
-- for each row

I just changed the printing message.
It printed the last (newest) trigger first!!!


p33e

(See p33d below)

Schema Level Triggers:

create or replace trigger schema1 
before create on geller.schema
begin
   dbms_output.put_line('creating...');
end;
/

Can create such triggers for:

LOGON LOGOFF CREATE DROP ALTER

But I did not get logon/logoff to work under SQLdeveloper.

----------------------------------------

You can have local variables in a trigger:

create or replace trigger trident10
before insert on x
for each row
declare
  num number;
begin
  num :=1 ;
  dbms_output.put_line(num);
end;
/