Trigger in mysql

Trigger in mysql

The trigger activates (INSERT, UPDATE, or DELETE), and whether it activates before or after rows are modified. 

A trigger is an ideal way of maintaining the values in this summary table.

The following example shows a trigger bi_t for INSERT statements for a table t that has an integer percent column for storing percentage values (0 to 100) and a DATETIME column.

The trigger uses BEFORE so that it can examine data values before they are inserted into the table.

CREATE TABLE t (percent INT, dt DATETIME); 

delimiter $
CREATE TRIGGER bi_t  BEFORE INSERT ON t
FOR EACH ROW BEGIN
SET NEW.dt = CURRENT_TIMESTAMP;
IF NEW.percent < 0 THEN
SET NEW.percent = 0;
ELSEIF NEW.percent > 100 THEN
SET NEW.percent = 100;
END IF;
END$
delimiter ;

For attempts to insert a percentage value that lies outside the range from 0 to 100, the trigger converts the value to the nearest endpoint.

Trigger to maintain a derived column value

1 CREATE TRIGGER employees_trg_bu
2 BEFORE UPDATE ON employees
3 FOR EACH ROW
4 BEGIN
5 IF NEW.salary <50000 THEN
6 SET NEW.contrib_401K=500;
7 ELSE
8 SET NEW.contrib_401K=500+(NEW.salary-50000)*.01;
9 END IF;
10 END


Automatically populate the contrib_401K column in the employees table. If the new value for the
salary column is less than 50000, the contrib._401K column will be set to 500. Otherwise, the value
will be calculated as shown in line 8.

Maintain the values in the customer_sales_totals table whenever there is an UPDATE, INSERT, or DELETE operation on the sales table. 

Using triggers to maintain denormalized data

DELIMITER $
CREATE TRIGGER sales_bi_trg
BEFORE INSERT ON sales
FOR EACH ROW
BEGIN
DECLARE row_count INTEGER;
SELECT COUNT(*)
INTO row_count
FROM customer_sales_totals
WHERE customer_id=NEW.customer_id;
IF row_count > 0 THEN
UPDATE customer_sales_totals
SET sale_value=sale_value+NEW.sale_value
WHERE customer_id=NEW.customer_id;
ELSE
INSERT INTO customer_sales_totals
(customer_id,sale_value)
VALUES(NEW.customer_id,NEW.sale_value);
END IF;
END$
-------------------------------------------------------------------------------------


CREATE TRIGGER sales_bu_trg
BEFORE UPDATE ON sales
FOR EACH ROW
BEGIN
UPDATE customer_sales_totals
SET sale_value=sale_value+(NEW.sale_value-OLD.sale_value)
WHERE customer_id=NEW.customer_id;
END$

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


CREATE TRIGGER sales_bd_trg
BEFORE DELETE ON sales
FOR EACH ROW
BEGIN
UPDATE customer_sales_totals
SET sale_value=sale_value-OLD.sale_value
WHERE customer_id=OLD.customer_id;
END$