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 //
