India

ZestyBeanz Technologies Pvt Ltd 4th Floor, Nila, Technopark Thiruvananthapuram,
India – 695581
Phone: +91 471 4063254
Fax : +91 471 2700171

   .

ZestyBeanz Technologies Pvt Ltd
2987, Manikkath Cross Road
Ravipuram, Kochi, India - 682016
Phone: +91 484 4063254

  UAE

Zesty Labs
Office # 2003, Millennium Plaza Building
Sheikh Zayed Rd, Dubai, UAE
Phone: +971 4333 2222​
Mobile: +971-56-2708993​

  Germany​

ZestyBeanz Technologies GmbH
Reuterstraße 1
90408 Nürnberg
Fon: +49 911 4801 444
Fax: +49 911 4801 445

Creating Sql Triggers

Contact Form


vijayan's picture

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";