trigger problem

17 Aug 2006 3:34 PM

Jesse Aufiero

Hello,

I'm trying to create a trigger that involves an ntext column and i'm running
into problems. Upon modification to a row in table A, a trigger should
determine if column A, which is an ntext column, was modified. If so, the
trigger should take the new value for column A and insert it into a column
in table B. The goal is to maintain an audit trail of changes made to
column A in a separate table B.

Below is the t-sql code i'm using for the trigger, and the resulting error
that occurs when i attempt to compile/create it...

Thank you!

--------------------------------------------------------------------------------------

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER tblDeals_trackConfirmChanges on tblDeals
FOR INSERT, UPDATE
AS

declare
@lvDealID int
,@lvSellerConfirm varchar(8000)
,@lvBuyerConfirm varchar(8000)

select
@lvDealID = DealID
,@lvSellerConfirm = SellerConfirm
,@lvBuyerConfirm = BuyerConfirm
from inserted

IF UPDATE(SellerConfirm)
INSERT INTO tblAuditConfirmChanges (ModDateTime, DealID, ConfirmType,
ConfirmText)
VALUES (getdate(), @lvDealID, 'S', @lvSellerConfirm)

IF UPDATE(BuyerConfirm)
INSERT INTO tblAuditConfirmChanges (ModDateTime, DealID, ConfirmType,
ConfirmText)
VALUES (getdate(), @lvDealID, 'B', @lvBuyerConfirm)

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

----------------------------------------------------------------------------------------

Server: Msg 311, Level 16, State 1, Procedure tblDeals_trackConfirmChanges,
Line 11
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
tables.
Server: Msg 311, Level 16, State 1, Procedure tblDeals_trackConfirmChanges,
Line 11
Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
tables.

17 Aug 2006 4:59 PM

Arnie Rowland

Since you cannot (as the error indicates) access the text/ntext fields in
the inserted/deleted virtual tables, you cannot use the UPDATED() function
in the TRIGGER.

Is the data in SellerConfirm and BuyerConfirm of such nature that it HAS to
be text/ntext?
Why not use varchar()?

If your fields were varchar(), the TRIGGER would work.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"Jesse Aufiero" <jaufi***@moaboil.com> wrote in message
news:%23HYu8NhwGHA.4160@TK2MSFTNGP06.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that involves an ntext column and i'm
> running into problems. Upon modification to a row in table A, a trigger
> should determine if column A, which is an ntext column, was modified. If
> so, the trigger should take the new value for column A and insert it into
> a column in table B. The goal is to maintain an audit trail of changes
> made to column A in a separate table B.
>
> Below is the t-sql code i'm using for the trigger, and the resulting error
> that occurs when i attempt to compile/create it...
>
> Thank you!
>
> --------------------------------------------------------------------------------------
>
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE TRIGGER tblDeals_trackConfirmChanges on tblDeals
> FOR INSERT, UPDATE
> AS
>
> declare
> @lvDealID int
> ,@lvSellerConfirm varchar(8000)
> ,@lvBuyerConfirm varchar(8000)
>
> select
> @lvDealID = DealID
> ,@lvSellerConfirm = SellerConfirm
> ,@lvBuyerConfirm = BuyerConfirm
> from inserted
>
> IF UPDATE(SellerConfirm)
> INSERT INTO tblAuditConfirmChanges (ModDateTime, DealID, ConfirmType,
> ConfirmText)
> VALUES (getdate(), @lvDealID, 'S', @lvSellerConfirm)
>
> IF UPDATE(BuyerConfirm)
> INSERT INTO tblAuditConfirmChanges (ModDateTime, DealID, ConfirmType,
> ConfirmText)
> VALUES (getdate(), @lvDealID, 'B', @lvBuyerConfirm)
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> ----------------------------------------------------------------------------------------
>
> Server: Msg 311, Level 16, State 1, Procedure
> tblDeals_trackConfirmChanges, Line 11
> Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
> tables.
> Server: Msg 311, Level 16, State 1, Procedure
> tblDeals_trackConfirmChanges, Line 11
> Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
> tables.
>
>
>
>
>
>

17 Aug 2006 5:04 PM

Jesse Aufiero

unfortunately the column must be of type ntext. the type varchar cannot fit
the lengths of text that need to be held in the column...

Show quote

"Arnie Rowland" <ar***@1568.com> wrote in message
news:urvEC6hwGHA.5056@TK2MSFTNGP06.phx.gbl...
> Since you cannot (as the error indicates) access the text/ntext fields in
> the inserted/deleted virtual tables, you cannot use the UPDATED() function
> in the TRIGGER.
>
> Is the data in SellerConfirm and BuyerConfirm of such nature that it HAS
> to be text/ntext?
> Why not use varchar()?
>
> If your fields were varchar(), the TRIGGER would work.
>
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
>
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
>
>
> "Jesse Aufiero" <jaufi***@moaboil.com> wrote in message
> news:%23HYu8NhwGHA.4160@TK2MSFTNGP06.phx.gbl...
>> Hello,
>>
>> I'm trying to create a trigger that involves an ntext column and i'm
>> running into problems. Upon modification to a row in table A, a trigger
>> should determine if column A, which is an ntext column, was modified. If
>> so, the trigger should take the new value for column A and insert it into
>> a column in table B. The goal is to maintain an audit trail of changes
>> made to column A in a separate table B.
>>
>> Below is the t-sql code i'm using for the trigger, and the resulting
>> error that occurs when i attempt to compile/create it...
>>
>> Thank you!
>>
>> --------------------------------------------------------------------------------------
>>
>> SET QUOTED_IDENTIFIER OFF
>> GO
>> SET ANSI_NULLS ON
>> GO
>>
>> CREATE TRIGGER tblDeals_trackConfirmChanges on tblDeals
>> FOR INSERT, UPDATE
>> AS
>>
>> declare
>> @lvDealID int
>> ,@lvSellerConfirm varchar(8000)
>> ,@lvBuyerConfirm varchar(8000)
>>
>> select
>> @lvDealID = DealID
>> ,@lvSellerConfirm = SellerConfirm
>> ,@lvBuyerConfirm = BuyerConfirm
>> from inserted
>>
>> IF UPDATE(SellerConfirm)
>> INSERT INTO tblAuditConfirmChanges (ModDateTime, DealID, ConfirmType,
>> ConfirmText)
>> VALUES (getdate(), @lvDealID, 'S', @lvSellerConfirm)
>>
>> IF UPDATE(BuyerConfirm)
>> INSERT INTO tblAuditConfirmChanges (ModDateTime, DealID, ConfirmType,
>> ConfirmText)
>> VALUES (getdate(), @lvDealID, 'B', @lvBuyerConfirm)
>>
>> GO
>> SET QUOTED_IDENTIFIER OFF
>> GO
>> SET ANSI_NULLS ON
>> GO
>>
>> ----------------------------------------------------------------------------------------
>>
>> Server: Msg 311, Level 16, State 1, Procedure
>> tblDeals_trackConfirmChanges, Line 11
>> Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
>> tables.
>> Server: Msg 311, Level 16, State 1, Procedure
>> tblDeals_trackConfirmChanges, Line 11
>> Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
>> tables.
>>
>>
>>
>>
>>
>>
>
>

17 Aug 2006 5:15 PM

Tom Cooper

The ideal solution is to migrate to SQL 2005 and change your ntext columns
to nvarchar(MAX). But, in the real world, that option is often not
available. But do it if you possibly can. Alternatively, does your data
really need to be ntext? If the data will fit in nvarchar or varchar
columns, you can use those, then you can reference these columns in your
trigger.

If you are in SQL 2000, only INSTEAD OF triggers can see text, image, or
ntext columns in the inserted and deleted tables. You have an AFTER trigger
which is why you are getting this error message. See the CREATE TRIGGER
topic in BOL.

Also, your code is only saving the first 8000 characters of these text
columns. That is fine if that is your business requirement, but if you need
the whole value, you're not getting it.

Finally, your trigger has the common error of only working correctly if an
UPDATE or INSERT statement updates or inserts exactly one row. Triggers are
called once per statement, not once per row affected by the statement. If,
for example, someone executed
Update tblDeals Set SellerConfirm = 'Whoops' Where ...
and that statement updated 100 rows, your trigger would be called once with
100 rows in the inserted table. Your code would audit only one of these 100
changes.

Tom

Show quote

"Jesse Aufiero" <jaufi***@moaboil.com> wrote in message
news:%23HYu8NhwGHA.4160@TK2MSFTNGP06.phx.gbl...
> Hello,
>
> I'm trying to create a trigger that involves an ntext column and i'm
> running into problems. Upon modification to a row in table A, a trigger
> should determine if column A, which is an ntext column, was modified. If
> so, the trigger should take the new value for column A and insert it into
> a column in table B. The goal is to maintain an audit trail of changes
> made to column A in a separate table B.
>
> Below is the t-sql code i'm using for the trigger, and the resulting error
> that occurs when i attempt to compile/create it...
>
> Thank you!
>
> --------------------------------------------------------------------------------------
>
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> CREATE TRIGGER tblDeals_trackConfirmChanges on tblDeals
> FOR INSERT, UPDATE
> AS
>
> declare
> @lvDealID int
> ,@lvSellerConfirm varchar(8000)
> ,@lvBuyerConfirm varchar(8000)
>
> select
> @lvDealID = DealID
> ,@lvSellerConfirm = SellerConfirm
> ,@lvBuyerConfirm = BuyerConfirm
> from inserted
>
> IF UPDATE(SellerConfirm)
> INSERT INTO tblAuditConfirmChanges (ModDateTime, DealID, ConfirmType,
> ConfirmText)
> VALUES (getdate(), @lvDealID, 'S', @lvSellerConfirm)
>
> IF UPDATE(BuyerConfirm)
> INSERT INTO tblAuditConfirmChanges (ModDateTime, DealID, ConfirmType,
> ConfirmText)
> VALUES (getdate(), @lvDealID, 'B', @lvBuyerConfirm)
>
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
>
> ----------------------------------------------------------------------------------------
>
> Server: Msg 311, Level 16, State 1, Procedure
> tblDeals_trackConfirmChanges, Line 11
> Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
> tables.
> Server: Msg 311, Level 16, State 1, Procedure
> tblDeals_trackConfirmChanges, Line 11
> Cannot use text, ntext, or image columns in the 'inserted' and 'deleted'
> tables.
>
>
>
>
>
>

17 Aug 2006 5:42 PM

Alexander Kuznetsov

Jesse,

declare
@lvDealID int
,@lvSellerConfirm varchar(8000)
,@lvBuyerConfirm varchar(8000)

select
@lvDealID = DealID
,@lvSellerConfirm = SellerConfirm
,@lvBuyerConfirm = BuyerConfirm
from inserted

select
,@lvSellerConfirm = SellerConfirm
,@lvBuyerConfirm = BuyerConfirm
from YourUnderlyingTable where @lvDealID = DealID

Note that it can only work if you insert/update one row at a time. You
might need a set based trigger.