Hi I have a bunch of triggers which back up data being deleted into another table. These work fine except for one table with ntext fields. I found that INSTEAD OF works with this however it does back up the table but doesn't delete the record. Lookign at the syntax I can't see anyway of modifying the syntax to delete the record any help appreciated thanks jc USE [IcisBaseWebsite] GO /****** Object: Trigger [dbo].[DeleteEdit_OrganisationInstead] Script Date: 07/02/2009 14:41:40 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER TRIGGER [dbo].[DeleteEdit_OrganisationInstead] ON [dbo].[Edit_Organisation] instead of DELETE AS INSERT INTO Edit_Organisation_Backup ( ....) SELECT getDate() .....FROM deleted
I've almost never used INSTEAD OF TRIGGER,but you are running SQL Server 2005 and onwards look up an OUTPUT clause create table t ( i int not null ,j ntext); create table t_audit ( old_i int ,old_j ntext ); insert into t (i,j) values( 1,'tetetetetetetetet'); insert into t (i,j) values( 2 ,'tetetetetetetetet'); delete from t output deleted.i, deleted.j into t_audit where i = 2; select * from t select * from t_audit "Jonathan Crawford" wrote in message news:OyR47sx%23JHA.1492@TK2MSFTNGP03.phx.gbl... > Hi > > I have a bunch of triggers which > back up data being deleted into another table. > > These work fine except for one table with > ntext fields. > > I found that INSTEAD OF works with this > however it does back up the table > but doesn't delete the record. > > Lookign at the syntax I can't see anyway of > modifying the syntax to delete the record > > any help appreciated > > thanks > > jc > > USE [IcisBaseWebsite] > GO > /****** Object: Trigger [dbo].[DeleteEdit_OrganisationInstead] Script > Date: 07/02/2009 14:41:40 ******/ > SET ANSI_NULLS ON > GO > SET QUOTED_IDENTIFIER ON > GO > > ALTER TRIGGER [dbo].[DeleteEdit_OrganisationInstead] ON > [dbo].[Edit_Organisation] > instead of > DELETE > AS > INSERT INTO Edit_Organisation_Backup ( ....) > SELECT getDate() .....FROM deleted >
You need to issue the statements to do the actual delete since this is an INSTEAD OF trigger. Something like: DELETE FROM [dbo].[Edit_Organisation] WHERE EXISTS( SELECT * FROM Inserted AS i WHERE i.PK = [dbo].[Edit_Organisation].[PK]) -- Andrew J. Kelly SQL MVP Solid Quality Mentors "Jonathan Crawford" wrote in message news:OyR47sx%23JHA.1492@TK2MSFTNGP03.phx.gbl... > Hi > > I have a bunch of triggers which > back up data being deleted into another table. > > These work fine except for one table with > ntext fields. > > I found that INSTEAD OF works with this > however it does back up the table > but doesn't delete the record. > > Lookign at the syntax I can't see anyway of > modifying the syntax to delete the record > > any help appreciated > > thanks > > jc > > USE [IcisBaseWebsite] > GO > /****** Object: Trigger [dbo].[DeleteEdit_OrganisationInstead] Script > Date: 07/02/2009 14:41:40 ******/ > SET ANSI_NULLS ON > GO > SET QUOTED_IDENTIFIER ON > GO > > ALTER TRIGGER [dbo].[DeleteEdit_OrganisationInstead] ON > [dbo].[Edit_Organisation] > instead of > DELETE > AS > INSERT INTO Edit_Organisation_Backup ( ....) > SELECT getDate() .....FROM deleted >
I did that and it worked which was great, thanks very much jc -- =============== Jonathan Crawford jc@tgsi.net =============== "Andrew J. Kelly" wrote in message news:%238gSMiy%23JHA.1380@TK2MSFTNGP02.phx.gbl... > You need to issue the statements to do the actual delete since this is an > INSTEAD OF trigger. Something like: > > DELETE FROM [dbo].[Edit_Organisation] WHERE EXISTS( > SELECT * FROM Inserted AS i WHERE i.PK = > [dbo].[Edit_Organisation].[PK]) > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "Jonathan Crawford" wrote in message > news:OyR47sx%23JHA.1492@TK2MSFTNGP03.phx.gbl... >> Hi >> >> I have a bunch of triggers which >> back up data being deleted into another table. >> >> These work fine except for one table with >> ntext fields. >> >> I found that INSTEAD OF works with this >> however it does back up the table >> but doesn't delete the record. >> >> Lookign at the syntax I can't see anyway of >> modifying the syntax to delete the record >> >> any help appreciated >> >> thanks >> >> jc >> >> USE [IcisBaseWebsite] >> GO >> /****** Object: Trigger [dbo].[DeleteEdit_OrganisationInstead] Script >> Date: 07/02/2009 14:41:40 ******/ >> SET ANSI_NULLS ON >> GO >> SET QUOTED_IDENTIFIER ON >> GO >> >> ALTER TRIGGER [dbo].[DeleteEdit_OrganisationInstead] ON >> [dbo].[Edit_Organisation] >> instead of >> DELETE >> AS >> INSERT INTO Edit_Organisation_Backup ( ....) >> SELECT getDate() .....FROM deleted >> >
