Problem with triggers an ntext

2 Jul 2009 1:44 PM

Jonathan Crawford

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

2 Jul 2009 1:54 PM

Uri Dimant

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

Show quoteHide quote

"Jonathan Crawford" <j*@jcrawford.co.uk> 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
>

2 Jul 2009 3:19 PM

Andrew J. Kelly

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

Show quoteHide quote

"Jonathan Crawford" <j*@jcrawford.co.uk> 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
>

6 Jul 2009 9:01 AM

Jonathan Crawford

I did that and it worked which was great,
thanks very much

jc

--
===============
Jonathan Crawford
j*@tgsi.net
===============

Show quoteHide quote

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> 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" <j*@jcrawford.co.uk> 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
>>
>