Part 8 : Trigger in PostgreSQL

Exercise for manage trigger

Step 1: Create table 

drop table test1,test2 ;  

create table test1 (id serial primary key ,name varchar);
create table test2 (id serial primary key ,name varchar);
 

Step 2: Create function for UPDATE DELETE INSERT Trigger 

create  or replace function log_insert_test1()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$$
begin    
    IF  NEW.name<>old.name then
    INSERT into test2 (name) values(old.name);
    else if old.name=old.name then
    INSERT into test2 (name) values(old.name);
    else
    INSERT into test2 (name) values(NEW.name);
    end if;
    end if;
RETURN NEW ;
end;
$$

Step 3: Create  trigger for insert

CREATE TRIGGER insert_test1 AFTER INSERT ON test1 FOR EACH ROW
execute procedure log_insert_test1();
 

Step 4: Create trigger for update

create trigger update_test1 after update on test1 for each row
execute procedure log_insert_test1();
 

Step 4: Create trigger for delete

CREATE TRIGGER delete_test1 AFTER DELETE ON test1 for each row
execute procedure log_insert_test1();

Perform Task   :

select * from test1 ;
select * from test2 ;
 

insert into test1 (name) values ('K') ; 

update test1 set name='K' where id=1 ;

delete from test1 where id=2 ;

2. Ex- display_salary_changes

Table :

select * from emp_details ;
select * from test2 ;

Step 2: Create function for update new salary audit table audit_table

create or replace function log_update_salary()
RETURNS TRIGGER
LANGUAGE plpgsql
as
$$
 begin
     if NEW.salary <> old.salary then
     insert into audit_table (id,name,salary,new_salary)
      values (old.id,old.emp_name,new.salary,new.salary - old.salary)  ;
    end if;
RETURN NEW;
end ;
$$  

Step 3: Create trigger for update new salary in emp_details

create trigger new_salary_update
BEFORE UPDATE
 ON emp_details
FOR EACH ROW
execute procedure  log_update_salary() 

#drop trigger new_salary_update on emp_details