quetzco (11/22/2009)
Very, Very useful.How can I avoid the problem of 'ntext', 'text', 'image' fields when executing:
IF @ACT = 'INSERT' INSERT [DBO].[AUDIT_BTPRJ_COSTIIMP] SELECT *,'INSERT' ,GETDATE() FROM INSERTED
IF @ACT = 'DELETE' INSERT [DBO].[AUDIT_BTPRJ_COSTIIMP] SELECT *,'DELETE' ,GETDATE() FROM DELETED
IF @ACT = 'UPDATE' INSERT [DBO].[AUDIT_BTPRJ_COSTIIMP] SELECT *,'UPDATE' ,GETDATE() FROM INSERTED
?
Antonio
Hi, yes this is a real conundrum. There are several limitations in SQL Server that make this impossible:
1. You can't refer in any way to text, ntext or image fields in the inserted or deleted tables.
2. You can't refer to the inserted or deleted tables in dynamic sql which means you can't build a select statement excluding the forbidden columns.
Can anyone see a way around this without having to explicitly name columns (which would render the solution non-generic)?