Trigger help (first, last or combining??)

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Trigger help (first, last or combining??)

Author

Topic

jose1lm
Yak Posting Veteran

70 Posts

Posted - 2005-08-02 : 16:13:03

I have created a new trigger (TRIG1) which will copy any changes done to a table into a new table (history). Here is TRIG1:


CREATE TRIGGER TRIG1
ON dbo.iso_wi
AFTER update AS
      BEGIN
      INSERT INTO iso_wi_history
         (audit_log_type,
	         audit_wi,
		 audit_wi_title,
		 audit_rev,
		 audit_rev_date,
		 audit_author,
		 audit_sup_approval,
	         audit_sc_trainer,
	         audit_cam_trainer,
	         audit_lut_trainer,
	         audit_sp_trainer,
	         audit_training_hrs)
         SELECT 'OLD', 
	         del.wi,
		 del.wi_title,
		 del.rev,
		 del.rev_date,
		 del.author,
		 del.sup_approval,
	         del.sc_trainer,
	         del.cam_trainer,
	         del.lut_trainer,
	         del.sp_trainer,
	         del.training_hrs
         FROM deleted del

      INSERT INTO iso_wi_history
         (audit_log_type,
	         audit_wi,
		 audit_wi_title,
		 audit_rev,
		 audit_rev_date,
		 audit_author,
		 audit_sup_approval,
	         audit_sc_trainer,
	         audit_cam_trainer,
	         audit_lut_trainer,
	         audit_sp_trainer,
	         audit_training_hrs)
         SELECT 'NEW',
	         ins.wi,
		 ins.wi_title,
		 ins.rev,
		 ins.rev_date,
		 ins.author,
		 ins.sup_approval,
	         ins.sc_trainer,
	         ins.cam_trainer,
	         ins.lut_trainer,
	         ins.sp_trainer,
	         ins.training_hrs
	 FROM inserted ins
   END

If I only use TRIG1, then the results are saved correctly. However, if I save/create another trigger (TRIG2), then the results for the trigger above gets a duplication of data. Here is TRIG2:


CREATE TRIGGER TRIG2
ON dbo.iso_wi
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
UPDATE U
  SET U.author = NullIf(I.author, -1),
  U.author_dept = NullIf(I.author_dept, -1),
  U.sup_approval = NullIf(I.sup_approval, -1),
  U.sc_trainer = NullIf(I.sc_trainer, -1),
  U.cam_trainer = NullIf(I.cam_trainer, -1),
  U.lut_trainer = NullIf(I.lut_trainer, -1),
  U.sp_trainer = NullIf(I.sp_trainer, -1)
FROM dbo.iso_wi U
JOIN inserted I
ON I.wi = U.wi

How can I fix the triggers in a way that TRIG2 fires first, then TRIG1 fires last. Or can they both be combined into a single trigger statement and do the TRIG2 code first then TRIG1??

Thanks,

JLM

mwjdavidson
Aged Yak Warrior

735 Posts

Posted - 2005-08-02 : 16:30:45

Hi
Have a look at sp_settriggerorder in BOL.

Mark

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources