Scribd
Upload
21 views

Procedure and Trigger in SQL

Uploaded by

Wanohi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
21 views

Procedure and Trigger in SQL

Uploaded by

Wanohi
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 1

CREATE PROCEDURE SelectAllCustomers @City nvarchar(30), @PostalCode nvarchar(10)

AS
SELECT * FROM Customers WHERE City = @City AND PostalCode = @PostalCode
GO;

EXEC SelectAllCustomers @City = 'London', @PostalCode = 'WA1 1DP';

------------

A trigger in MySQL is a set of SQL statements that reside in a system catalog.


It is a special type of stored procedure that is invoked automatically in response
to an event.
Each trigger is associated with a table, which is activated on any DML statement
such as INSERT, UPDATE, or DELETE.

We can define the maximum six types of actions or events in the form of triggers:

Before Insert: It is activated before the insertion of data into the table.
After Insert: It is activated after the insertion of data into the table.
Before Update: It is activated before the update of data in the table.
After Update: It is activated after the update of the data in the table.
Before Delete: It is activated before the data is removed from the table.
After Delete: It is activated after the deletion of data from the table.
When we use a statement that does not use INSERT, UPDATE or DELETE query to change
the data in a table, the triggers associated with the trigger will not be invoked.

CREATE TRIGGER trigger_name


(AFTER | BEFORE) (INSERT | UPDATE | DELETE)
ON table_name FOR EACH ROW
BEGIN
--variable declarations
--trigger code
END;

mysql> DELIMITER //
mysql> Create Trigger before_insert_empworkinghours
BEFORE INSERT ON employee FOR EACH ROW
BEGIN
IF NEW.working_hours < 0 THEN SET NEW.working_hours = 0;
END IF;
END //

You might also like