Sql-server – Should I add SET NOCOUNT ON to all the triggers

1. DML - refers to data manipulation (update, insert, delete). A DML trigger is a trigger created to happen on update, insert or delete of table data.

The update function is the part of a DML trigger where you test if a required update has occured.

For Example:

CREATE TRIGGER myupdate_trigger
ON mytable
FOR UPDATE AS
if UPDATE(column_1)
BEGIN
-- Do something
END

This will trigger a further action every time column column_1 is updated in table mytable. The update function is UPDATE(column_1).

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

• Dec 23, 2011 : check constraint does not work?
• Apr 25, 2011 : Trigger in MySQL to prevent insertion

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END// 

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;  

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.