Trigger question for partial rollback

• Bullfrog

  SSC Eights!

  Points: 895

  More actions

  October 22, 2005 at 7:16 am

  #95463

  Hello:

  My trigger fires when I want it to but if I have a transaction with multiple INSERT statements it wipes them all out.  I only want it to ignore the INSERTS that meet a certain criteria (dscrptn = garbage or trash).  Anything else should be allowed into tblMyLog.  Code below is what I have thus far. SQL Server 2000.

  Ideas? 

  CREATE TRIGGER [trgTest] ON [dbo].[tblMyLog]

  FOR INSERT

  AS

  DECLARE @desc varchar(132)

  SELECT @desc = dscrptn

  FROM inserted

  IF (@desc = 'garbage') or (@desc = 'trash')

  BEGIN

     RAISERROR ('INSERT Ignored (trgTest)', 16, 1)

     ROLLBACK TRANSACTION

  END

  Thank you

• Carl Federl

  One Orange Chip

  Points: 25384

  More actions

  October 22, 2005 at 8:53 am

  #599357

  Do not use an "AFTER INSERT" trigger, but try using an "INSTEAD OF INSERT" trigger

  that inserts into the same table.

  CREATE TRIGGER [trgTest]

  ON [dbo].[tblMyLog]

  FOR INSTEAD OF INSERT

  AS

  set nocount on

  set xact_abort on

  if @@ROWCOUNT = 0 RETURN

  INSERT into tblMyLog

  select *

  from inserted

  where dscrptn not in ('garbage', 'trash')

  go

  SQL = Scarcely Qualifies as a Language