Hi,
I wanna create the trigger on one table that contains one image field but
when I declare that image variable in trigger, it gave me an error that I
can't define image datatype into local variable.

Is there any way I can write the trigger to do the auditing of the image
field as well?

Thanks in advance

[quoted text, click to view]

Hi Rogers,

First, you shouldn't declare variables in your trigger at all. A trigger
fires ones per statement executed, not once per row affected. Since all
statements can affect more than one row at once, your trigger has to be
able to deal with more than one row in the inserted and/or deleted
pseudo-tables. Using variables is a sign of either expecting just one
row (will break if you ever affect more than one), or using a cursor or
similar construct to loop over all rows and process them one by one (in
almost all cases needlessly slow). Your trigger should be set-based.

For image data, there's an extra problem - you won't find it in the
inserted and deleted pseudo-tables (except in an INSTEAD OF trigger, but
that's hardly a good choice for auditing). You'll have to get it from
the base table, by joining it to inserted (or deleted) in the trigger.

Rought outline of an auditing trigger, assuming a single-column primary
key:

CREATE TRIGGER Example
ON YourTable AFTER INSERT
AS
INSERT INTO AuditTable
(PKCol, OtherCol, ImageCol,
WhoDunnit, When)
SELECT i.PKCol, i.OtherCol, b.ImageCol,
USER_NAME(), CURRENT_TIMESTAMP
FROM inserted AS i
INNER JOIN YourTable AS b
ON b.PKCol = i.PKCol
go

Note: in a real trigger, you'd want to add some error handling.

--