|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Delete trigger not workingmirror-image table for it & put a trigger on the original table. This allows some me to do some diagnosis as well as recover records that should not have been deleted. The only problem is, my mirror table is never getting anything put in it. What's wrong with my trigger? CREATE Trigger InvPODetail_DTrig On InvPODetail For Delete AS Insert Into InvPODetail_Deleted (PONumber, BOSuffix, Product, Color, CostEach, SizeCode, Qty, company, OtherSize, LineNumber, Units, VendorNumber, QtyReceived, Size, DateExpected, Closed, Comment, DeliveryDate, DateCreated) (Select PONumber, BOSuffix, Product, Color, CostEach, SizeCode, Qty, company, OtherSize, LineNumber, Units, VendorNumber, QtyReceived, Size, DateExpected, Closed, Comment, DeliveryDate, DateCreated From deleted ) Thanks! dwilson Make sure the application/script is not using ALTER TABLE .. DISABLE TRIGGER
... before issuing the delete statement. Only way to find this is using pofiler to track the activity on this table. -- Show quoteThanks & Rate the Postings. -Ravi- "Daniel Wilson" wrote: > I have a table that's getting stuff deleted somehow. So I've created a > mirror-image table for it & put a trigger on the original table. This > allows some me to do some diagnosis as well as recover records that should > not have been deleted. > > The only problem is, my mirror table is never getting anything put in it. > What's wrong with my trigger? > > > CREATE Trigger InvPODetail_DTrig On InvPODetail For Delete > AS > Insert Into InvPODetail_Deleted > (PONumber, BOSuffix, Product, Color, CostEach, SizeCode, Qty, company, > OtherSize, LineNumber, Units, > VendorNumber, QtyReceived, Size, DateExpected, Closed, Comment, > DeliveryDate, DateCreated) > (Select PONumber, BOSuffix, Product, Color, CostEach, SizeCode, Qty, > company, OtherSize, LineNumber, Units, > VendorNumber, QtyReceived, Size, DateExpected, Closed, Comment, > DeliveryDate, DateCreated > From deleted ) > > Thanks! > > dwilson > > > No, that's not the issue. To test I issue a
Delete From InvPODetail WHERE ID = <some number> from Query Analyzer. The original table loses the record, but nothing gets inserted into then mirror. Thanks for the idea, though. dwilson Show quote "Ravi" <ravishank***@hotmail.com> wrote in message news:AF351716-BE36-4B21-B43E-37634BE26D5A@microsoft.com... > Make sure the application/script is not using ALTER TABLE .. DISABLE TRIGGER > .. before issuing the delete statement. Only way to find this is using > pofiler to track the activity on this table. > > > -- > Thanks & Rate the Postings. > -Ravi- > > > "Daniel Wilson" wrote: > > > I have a table that's getting stuff deleted somehow. So I've created a > > mirror-image table for it & put a trigger on the original table. This > > allows some me to do some diagnosis as well as recover records that should > > not have been deleted. > > > > The only problem is, my mirror table is never getting anything put in it. > > What's wrong with my trigger? > > > > > > CREATE Trigger InvPODetail_DTrig On InvPODetail For Delete > > AS > > Insert Into InvPODetail_Deleted > > (PONumber, BOSuffix, Product, Color, CostEach, SizeCode, Qty, company, > > OtherSize, LineNumber, Units, > > VendorNumber, QtyReceived, Size, DateExpected, Closed, Comment, > > DeliveryDate, DateCreated) > > (Select PONumber, BOSuffix, Product, Color, CostEach, SizeCode, Qty, > > company, OtherSize, LineNumber, Units, > > VendorNumber, QtyReceived, Size, DateExpected, Closed, Comment, > > DeliveryDate, DateCreated > > From deleted ) > > > > Thanks! > > > > dwilson > > > > > > Hi,
three ideas, 1) try putting a rollback in the trigger and see if that works or a print statement PRINT 'In the After Trigger' 2) I always prefer to specify the trigger as an after trigger rather than the old for syntax CREATE TRIGGER TriggerOne ON Person AFTER Insert AS ... 3) run SELECT SubString(S2.Name,1,30) as [Table], SubString(S.Name, 1,30) as [Trigger], CASE (SELECT -- Correlated subquery OBJECTPROPERTY(OBJECT_ID(S.Name), 'ExecIsTriggerDisabled')) WHEN 0 THEN 'Enabled' WHEN 1 THEN 'Disabled' END AS Status FROM Sysobjects S JOIN Sysobjects S2 ON S.parent_obj = S2.ID WHERE S.Type = 'TR' ORDER BY [Table], [Trigger] to see if the trigger was previously disabled. Show quote "Daniel Wilson" <d.wil***@embtrak.com> wrote in message news:%238z1QHxgFHA.1416@TK2MSFTNGP09.phx.gbl... > No, that's not the issue. To test I issue a > Delete From InvPODetail WHERE ID = <some number> > from Query Analyzer. The original table loses the record, but nothing gets > inserted into then mirror. > > Thanks for the idea, though. > > dwilson > "Ravi" <ravishank***@hotmail.com> wrote in message > news:AF351716-BE36-4B21-B43E-37634BE26D5A@microsoft.com... >> Make sure the application/script is not using ALTER TABLE .. DISABLE > TRIGGER >> .. before issuing the delete statement. Only way to find this is using >> pofiler to track the activity on this table. >> >> >> -- >> Thanks & Rate the Postings. >> -Ravi- >> >> >> "Daniel Wilson" wrote: >> >> > I have a table that's getting stuff deleted somehow. So I've created a >> > mirror-image table for it & put a trigger on the original table. This >> > allows some me to do some diagnosis as well as recover records that > should >> > not have been deleted. >> > >> > The only problem is, my mirror table is never getting anything put in > it. >> > What's wrong with my trigger? >> > >> > >> > CREATE Trigger InvPODetail_DTrig On InvPODetail For Delete >> > AS >> > Insert Into InvPODetail_Deleted >> > (PONumber, BOSuffix, Product, Color, CostEach, SizeCode, Qty, company, >> > OtherSize, LineNumber, Units, >> > VendorNumber, QtyReceived, Size, DateExpected, Closed, Comment, >> > DeliveryDate, DateCreated) >> > (Select PONumber, BOSuffix, Product, Color, CostEach, SizeCode, Qty, >> > company, OtherSize, LineNumber, Units, >> > VendorNumber, QtyReceived, Size, DateExpected, Closed, Comment, >> > DeliveryDate, DateCreated >> > From deleted ) >> > >> > Thanks! >> > >> > dwilson >> > >> > >> > > >  |
|||||||||||||||||||||||
Other interesting topics