text, ntext and image datatypes in triggers

Question

text, ntext and image datatypes in triggers

Vani M

SSCrazy

Points: 2016
More actions
June 5, 2009 at 5:09 am

#222045

I was working with triggers on tables that had columns with text and image datatypes. To access these columns from inserted and deleted tables, I had to use INSTEAD OF triggers rather than AFTER triggers.
It just got me wondering, are the pseudotables initialized differently for INSTEAD OF and AFTER triggers? Any idea how this works behind the scenes?
And again, why are they different?
Regards,
Vani

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004639 More actions · Answer 1

I don't know the answer to your question, but I'd love to see your trigger code for this. I've always had a bit of a problem with InsteadOf triggers and it would be good to see a working example. Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Vani M SSCrazy Points: 2016 More actions · Answer 2

Jeff,

Here is the trigger code. It was an audit requirement, where the old and new values had to be stored. Update to the base table is done explicitly. The code itself is not very great, but it seems to work.

CREATE TRIGGER [TestTbl_BeforeUpdateTrigger]

ON [TestDB].[dbo].[TestTbl]

INSTEAD OF UPDATE

AS

BEGIN

SET NOCOUNT ON;

BEGIN

IF UPDATE (TestCol7)

BEGIN

INSERT INTO Audit.dbo.AuditTbl (SrcDBName, SrcTblName, PrimaryKey,PrimaryKeyValue,ColumnName, DataType,UpdateDatetime, Flag,NewImageValue,OldImageValue)

SELECT 'TestDB','TestTbl','PKName', PKName,'TestCol7','image',getdate(),'2', I.TestCol7, D.TestCol7 FROM Inserted I JOIN Deleted D ON I.[PKName] = D.[PKName]

UPDATE TestTbl SET TestCol7 = D.TestCol7

FROM TestTbl I JOIN Inserted D ON I.[PKName] = D.[PKName]

END

IF UPDATE (TestCol6)

BEGIN

INSERT INTO Audit.dbo.AuditTbl (SrcDBName, SrcTblName, PrimaryKey,PrimaryKeyValue,ColumnName, DataType,UpdateDatetime, Flag,NewValue,OldValue)

SELECT 'TestDB','TestTbl','PKName', PKName,'TestCol6','text',getdate(),'2', I.TestCol6, D.TestCol6 FROM Inserted I JOIN Deleted D ON I.[PKName] = D.[PKName]

UPDATE TestTbl SET TestCol6 = D.TestCol6

FROM TestTbl I JOIN Inserted D ON I.[PKName] = D.[PKName]

END

GO

Regards,

Vani