trigger not works when table at another schema than dbo

[baur]

trigger not works when table at another schema than dbo
please, see my forked version

[doxakis]

Hi,

Great work! Maybe we can merge what you have done when you are done. (see at the end my comments)

But first, let's address the issue. If you take a look in the sql trigger (table), you will find:

SELECT
    @field = MIN(ORDINAL_POSITION),
    @char = (column_id - 1) / 8 + 1,
    @mask = POWER(2, (column_id - 1) % 8),
    @fieldname = name
FROM SYS.COLUMNS SC
INNER JOIN INFORMATION_SCHEMA.COLUMNS ISC
ON SC.name = ISC.COLUMN_NAME
WHERE object_id = OBJECT_ID(@TableName)
AND TABLE_NAME = @TableName
AND ORDINAL_POSITION = @field
GROUP BY column_id, name

The issue is : WHERE object_id = OBJECT_ID(@TableName)
It should be : WHERE object_id = OBJECT_ID(@TableSchema+'.'+@TableName)

My comments :

• Install audit trail script for SQL Server Database.sql : remove USE BILLING
• Improve docs to help better understand which script to run and the order of script execution (if applicable)
• When I insert/update/delete rows in the AuditTables table, I have to run "Install audit trail script for SQL Server Database.sql" to update the trigger. (maybe it could be great to have a sql trigger in the AuditTables table. So, it makes development and maintenance easier.

I can help with the documentation.

Thanks!

[baur]

thank you, I'll try it,
yes, I need help with the documentation ...

[baur]

that works fine, but rise an error when same table name in different schema (((
also gets error when deleting trigger located in other schema than dbo

https://www.red-gate.com/simple-talk/sql/database-administration/pop-rivetts-sql-server-faq-no.5-pop-on-the-audit-trail/
comment:
dejandular • 5 years ago
solved this but not works for me