How to use "Error handling in a trigger"?

• bp

  SSCarpal Tunnel

  Points: 4656

  More actions

  January 27, 2003 at 2:03 am

  #106132

  Is it possible to log an error generated by a FK constraint when a trigger tries to perform an INSERT?

  For example:

  --------- cut here ------------

  USE TEMPDB

  GO

  SET NOCOUNT ON

  /* create parent and child table */

  CREATE TABLE STAFF(STAFF_ID INT PRIMARY KEY, STAFF_NAME VARCHAR(10));

  CREATE TABLE HOURS(HOURS_ID INT IDENTITY(1,1), STAFF_ID INT, WORKED_HRS INT);

  ALTER TABLE HOURS ADD CONSTRAINT FK_HRS_STAFF FOREIGN KEY (STAFF_ID) REFERENCES STAFF(STAFF_ID) ;

  GO

  CREATE TRIGGER TR_HOURS ON HOURS AFTER INSERT AS

  BEGIN

  INSERT INTO HOURS(STAFF_ID, WORKED_HRS) VALUES(1,10);

  END

  GO

  INSERT INTO STAFF(STAFF_ID, STAFF_NAME) VALUES(1,'alan');

  INSERT INTO HOURS(STAFF_ID, WORKED_HRS) VALUES(1,3);

  SELECT * FROM HOURS

  GO

  ALTER TRIGGER TR_HOURS ON HOURS AFTER INSERT AS

  BEGIN

  /* insert into Hours a staff_id that does not exist */

  INSERT INTO HOURS(STAFF_ID, WORKED_HRS) VALUES(2,10);

  END

  GO

  /* foreign key constraint will prevent the insert in trigger to finish */

  INSERT INTO HOURS(STAFF_ID, WORKED_HRS) VALUES(1,3);

  SELECT * FROM HOURS

  GO

  DROP TABLE HOURS;

  DROP TABLE STAFF;

  GO

  SET NOCOUNT OFF

  --------- cut here ------------

  thanks in advance

  Billy

• January 30, 2003 at 8:00 am

  This was removed by the editor as SPAM