EHqdsEg9

1CREATE TRIGGER mytabletriggerexample
2BEFORE INSERT
3FOR EACH ROW BEGIN
4IF(NEW.important_value) < (fancy * dancy * calculation) THEN
5    DECLARE dummy INT;
6
7    SELECT Your meaningful error message goes here INTO dummy 
8        FROM mytable
9      WHERE mytable.id=new.id
10END IF; END; 
11     
12DELIMITER $$
13DROP TRIGGER IF EXISTS before_tblinventoryexceptionreasons_delete $$
14CREATE TRIGGER before_tblinventoryexceptionreasons_delete
15BEFORE DELETE ON tblinventoryexceptionreasons
16FOR EACH ROW BEGIN
17  IF (SELECT COUNT(*) FROM tblinventoryexceptions WHERE tblinventoryexceptions.idtblinventoryexceptionreasons = old.idtblinventoryexceptionreasons) > 0
18  THEN
19    SET NEW='Error: Cannot delete this item. There are records in the inventory exception reasons table with this item.';
20  END IF;
21END$$
22DELIMITER ;
23
24DELIMITER $$
25DROP TRIGGER IF EXISTS before_storesalesconfig_delete $$
26CREATE TRIGGER before_storesalesconfig_delete
27BEFORE DELETE ON tblstoresalesconfig
28FOR EACH ROW BEGIN
29  IF (SELECT COUNT(*) FROM tblstoresales WHERE tblstoresales.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
30  THEN
31    SET NEW='Error: Cannot delete this item. There are records in the sales table with this item.';
32  END IF;
33  IF (SELECT COUNT(*) FROM tblinventory WHERE tblinventory.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
34  THEN
35    SET NEW='Error: Cannot delete this item. There are records in the inventory table with this item.';
36  END IF;
37  IF (SELECT COUNT(*) FROM tblinventoryexceptions WHERE tblinventoryexceptions.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
38  THEN
39    SET NEW='Error: Cannot delete this item. There are records in the inventory exceptions table with this item.';
40  END IF;
41  IF (SELECT COUNT(*) FROM tblinvoicedetails WHERE tblinvoicedetails.idtblstoresalesconfig=old.idtblstoresalesconfig) > 0
42  THEN
43    SET NEW='Error: Cannot delete this item. There are records in the inventory details table with this item.';
44  END IF;
45END$$
46DELIMITER ;
47
48DELIMITER $$
49DROP TRIGGER IF EXISTS before_tblinvoice_delete $$
50CREATE TRIGGER before_tblinvoice_delete
51BEFORE DELETE ON tblinvoice
52FOR EACH ROW BEGIN
53  IF (SELECT COUNT(*) FROM tblinvoicedetails WHERE tblinvoicedetails.idtblinvoice = old.idtblinvoice) > 0
54  THEN
55    SET NEW='Error: Cannot delete this item. There are records in the inventory details table with this item.';
56  END IF;
57END$$
58DELIMITER ; 
59     
60CREATE TRIGGER `my_table_AINS` AFTER INSERT ON `my_table` FOR EACH ROW
61BEGIN
62    DECLARE EXIT HANDLER FOR SQLEXCEPTION
63        RESIGNAL;
64    DECLARE EXIT HANDLER FOR SQLWARNING
65        RESIGNAL;
66    DECLARE EXIT HANDLER FOR NOT FOUND
67        RESIGNAL; 
68    -- Do the work of the trigger.
69END 
70     
71DROP PROCEDURE IF EXISTS `MyRaiseError`$$
72
73CREATE PROCEDURE `MyRaiseError`(msg VARCHAR(62))
74BEGIN
75DECLARE Tmsg VARCHAR(80);
76SET Tmsg = msg;
77IF (CHAR_LENGTH(TRIM(Tmsg)) = 0 OR Tmsg IS NULL) THEN
78SET Tmsg = 'ERROR GENERADO';
79END IF;
80SET Tmsg = CONCAT('@@MyError', Tmsg, '@@MyError');
81SET @MyError = CONCAT('INSERT INTO', Tmsg);
82PREPARE stmt FROM @MyError;
83EXECUTE stmt;
84DEALLOCATE PREPARE stmt;
85END$$ 
86     
87call MyRaiseError('Here error message!'); 
88     
89IF UPDATE(column_name)
90BEGIN
91  RAISEERROR
92  ROLLBACK TRAN
93  RETURN
94END