Sql-server – Triggers and the Transaction

sql serversql-server-2005transactiontrigger

We recently made a change to create constraints based on logic previously used in stored procedures, and part of that included the use of INSTEAD OF triggers to centralize logic.

The logic is generally simplistic:

The stored procedure contains an INSERT (or UPDATE) statement against a view, which has INSTEAD OF INSERT/UPDATE triggers
The trigger contains the actual logic — usually two statements, dealing with INSERT/UPDATE of a record into a main table and one (or more) supporting tables (incl. referential integrity).

The problem is when something like a CHECK constraint is hit within the statement in the trigger. The transaction gets dumped, and everything is rolled back. I'm looking at having to duplicate the CHECK/etc constraint in the offending stored procedures to validate prior to the trigger executing — is there any alternative?

Best Answer

Probably not. Violating a declarative constraint will always raise an error and prevent the transaction from committing writes that would violate the constraints. The point of database constraints - checks, foreign keys, uniques etc. - is that they prevent invalid data from being recorded in the database by aborting writes that will violate the constraints. The constraint violation raises an error and rolls back the transaction.

If you want the transaction to fail gracefully you will either have to deal with the exception or pre-validate the data before attempting to write it. In the latter case you will have to replace the validations in the stored procedures or implement validation in the application.

Related Solutions

Sql-server – Restrict update on certain columns. Only allow stored procedure to update those columns

SQL Server allows column-level permissions. Just for example:

GRANT UPDATE ON dbo.Person (FirstName, LastName) TO SampleRole;
DENY UPDATE ON dbo.Person (Age, Salary) TO SampleRole;

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

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.

Best Answer

Related Solutions

Sql-server – Restrict update on certain columns. Only allow stored procedure to update those columns

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

Related Question