Question

Perform the queries for triggers.

Answer 1

TRIGGERS

TRIGGERS : A SQL trigger is a database object just like a stored procedure, or we can say it is a special kind of stored procedure which fires when an event occurs in a database. We can execute a SQL query that will "do something" in a database when an event is fired.

Types of Triggers

1. DDL Trigger
2. DML Trigger

TRIGGERS	PROCEDURES
They are automatically executed on occurrence of specified event.	They can be executed whenever required.
Triggers can't be called inside a procedure.	But, you can call a procedure inside a trigger.
We can not pass parameters to triggers.	We can pass parameters to procedures.
Trigger never return value on execution.	Procedure may return value/s on execution.

Creating a trigger function : 

CREATE FUNCTION trigger_function()        RETURNS trigger AS

SQL CREATE TRIGGER statement :

CREATE TRIGGER trigger_name {BEFORE | AFTER | INSTEAD OF} {event [OR ...]}    ON table_name    [FOR [EACH] {ROW | STATEMENT}]        EXECUTE PROCEDURE trigger_function

Example : 

CREATE TABLE employees(    id SERIAL PRIMARY KEY,    first_name VARCHAR(40) NOT NULL,    last_name VARCHAR(40) NOT NULL );

INSERT INTO employees (first_name, last_name) VALUES ('John', 'Doe'); INSERT INTO employees (first_name, last_name) VALUES ('Lily', 'Bush');

SELECT * FROM employees;

Output : 

id	first_name	last_name
1	john	doe
2	lily	blush

UPDATE QUERY : 

UPDATE employees SET last_name = 'Brown' WHERE ID = 2;

SELECT * FROM employees;

Output : 

id	first_name	last_name
1	john	doe
2	lily	brown

Advantages of Triggers :

• Trigger generates some derived column values automatically
• Enforces referential integrity
• Event logging and storing information on table access
• Auditing
• Synchronous replication of tables
• Imposing security authorizations
• Preventing invalid transactions

---

For more Manipal University Jaipur CSE-IV Sem DBMS Lab Experiments CLICK HERE