Creating Sql Triggers

Introduction

Triggers are sql procedures that are executed on some events like insert, update or delete in a table or view. We can associate sql procedures with trigger so that we can have details like what data did  change during the triggering event. The data can be availed in some other tables if required. I would share you the basuc sql queries to create  and drop a trigger.

Creating a Trigger

CREATE TRIGGER log_trigger BEFORE INSERT OR UPDATE OR DELETE ON
addressbook FOR EACH ROW EXECUTE PROCEDURE update_log();
 

As sql is sequential query system. we must define the function update_log() before defining the trigger like
 

CREATE OR REPLACE FUNCTION update_log()
RETURNS TRIGGER AS $log_trigger$
DECLARE
new_name varchar; new_phonenum varchar;
BEGIN
IF(TG_OP= ‘DELETE’ ) THEN
INSERT INTO update_table(String,action,record_id) values (OLD.name,’DELETE’,OLD.id);
RETURN OLD;
END IF;
IF(TG_OP IN (‘UPDATE’ ,’INSERT’)) THEN
INSERT INTO update_table(String,action,record_id) values (new.name,TG_OP,new.id);
RETURN NEW;
END IF;
END;
$log_trigger$ LANGUAGE plpgsql;

Dropping a trigger from a table

DROP TRIGGER log_trigger ON addressbook;

This just removes the association b/w table and trigger. But if you want to entirely get rid of a trigger you can delete the record corresponding to the trigger from table “pg_trigger” like
DELETE FROM pg_trigger WHERE tgname = “your_trigger_name”;