December 11, 2006 at 9:01 am
I have created an Audit table to store data from a base table whenever an insert or update or delete occurs in the base table with the help of trigger.
The problem is base table has a column with datatype image. Trigger doesnt allow me to insert the image column into the audit table.
Do we have any other option in SQL to implement this scenario??
December 11, 2006 at 10:14 am
Did you try INSTEAD OF triggers?
Regards,Yelena Varsha
December 11, 2006 at 10:18 am
Look the artical.
http://msdn2.microsoft.com/en-us/library/ms189799.aspx
In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is set to 70. The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table. When the compatibility level is 65 or lower, null values are returned for inserted or deleted text, ntext, or image columns that allow null values; zero-length strings are returned if the columns are not nullable.
If the compatibility level is 80 or higher, SQL Server allows for the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.
cheers
December 12, 2006 at 4:16 am
Hi ijaz, I have seen that already. Anyway thanks buddy. There should be some other alternate right??
Hi Yelena, Why Should I go for INSTEAD OF Triggers???
December 12, 2006 at 10:56 am
INSTEAD OF triggers do something instead of the intended action. You may try to custom-code your something to include your Image column. Instead Of triggers are not nested, so if your insert triggered Instead Of trigger, then if as a part of the trigger you do an insert it does not trigger the second time. Try if it will work for you.
Regards,Yelena Varsha
December 12, 2006 at 2:20 pm
Copying images to audit table is not such a brilliant idea.
It's huge overhead which will slow down the system significantly.
But if you really sure you need to do it then take image value not from inserted tables but from static table joined to inserted.
If you need to audit deleted rows then use INSTEAD OF DELETE trigger. Make sure you have set nested_triggers to OFF.
_____________
Code for TallyGenerator
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply