SQL_TRIGGER1.htm
Oracle  And PostgreSQL :Triggers

Both Oracle and PostgreSQL have triggers and the create trigger statement is similar, but the code executed by the trigger for PostgreSQL must be in a stored function written by the user, while  Oracle has the option of write "create trigger statement" .

OBJECTIVES: trigger - on an event of updating a table
  • Step1: creating log table
    • ORACLE:
      create table emp_copy1_log(owner varchar2(30),time date)
    • POSTGRESQL
      postgres=# SELECT current_time, 'now'::time AS time;
  • Step 2: creating copy of emp table
     create table emp_copy1 as select * from emp
  • Step3 : creating trigger using DDL tool
    create trigger emp_biud_copy1
    before insert or update or delete
    on emp_copy1
    begin
    insert into emp_copy1_log( owner, time )
    end
  • Step 4 : Update and Query emp_copy1 table
    • //update using DDL TOOL
      update emp_copy1 set sal = sal * 2;
      //CONFIRM THE CHANGES
      //USING SQL QUERY (DML) TOOL
      select * from emp_copy1
       
  • Step 5 : check emp_copy1_log table
    • select * from emp_copy1_log;
  • OPTIONAL STEPS :
  • Step 6 Dropping emp_copy1
    • DROP TABLE  emp_copy1
    • check emp_copy1_log table
  • Step 7 droping  emp_copy1_log
    •  DROP TABLE emp_copy1_log
ORACLE EMP TABLE;
Create table which will be manipulated with a trigger, to an event of updating data/row in another table.

create table emp_copy1_log(owner varchar2(30),time date)

Now if we query the above table , table was empty.

Step 2:creating table emp_copy1

emp_copy1

Step 3: creating trigger:

create or replace trigger emp_biud_copy1 before insert or update or delete on emp_copy1 begin insert into emp_copy1_log( owner, time ) values( user, sysdate ); end;

Now Update the table emp_copy1

update emp_copy1 set sal = sal*1.1 /* ( no semicolon)*/

Verifying updates:
Testing the triggering effect