koenmd / autoaudit Goto Github PK

The triggers created by the project uses table variables, which defaults to 1 row from a query planning perspective. Since the triggers are pre-compiled, they need to know the number of rows in order to make a good plan.

The easiest way to achieve this is to add a OPTION RECOMPILE in the trigger creation procedure in all the places where the table variable is used. And then recreate all the triggers.

The performance will be 1000 times better after this update, for multirow updates.

Is there some exec command to remove autoAudit from a table? Or just deleting the triggers is how to do that? I actually don't care about any history data that I have, as I'm still in development and test on a particular table.

There seems to be an issue as described here: https://stackoverflow.com/questions/76863100/false-error-cannot-find-the-object-table-because-it-does-not-exist-or-you-do#76863100. This happens when using the SSMS designer to add a column in the middle of the existing columns (as opposed new columns at the end of the existing columns).

The error shown in the designer is misleading:
"Cannot find the object "xxxxx" because it does not exist or you do not have permissions."

I created the script from the designer as recommended in one of the comments, and it show this:

Dropping AutoAudit components from table: [dbo].[Ch_BizTalk_Email_DistList]
Dropping Table Audit DDL
Msg 4902, Level 16, State 1, Line 67
Cannot find the object "dbo.Ch_BizTalk_Email_DistList" because it does not exist or you do not have permissions.
Msg 15248, Level 11, State 1, Procedure sp_rename, Line 419 [Batch Start Line 68]
Either the parameter @OBJName is ambiguous or the claimed @objtype (OBJECT) is wrong.
Msg 1779, Level 16, State 0, Line 71
Table 'Ch_BizTalk_Email_DistList' already has a primary key defined on it.
Msg 1750, Level 16, State 0, Line 71
Could not create constraint or index. See previous errors.
Msg 2714, Level 16, State 2, Procedure Ch_BizTalk_Email_DistList_Audit_Insert, Line 1 [Batch Start Line 77]
There is already an object named 'Ch_BizTalk_Email_DistList_Audit_Insert' in the database.
Msg 2714, Level 16, State 2, Procedure Ch_BizTalk_Email_DistList_Audit_Update, Line 1 [Batch Start Line 231]
There is already an object named 'Ch_BizTalk_Email_DistList_Audit_Update' in the database.
Msg 2714, Level 16, State 2, Procedure Ch_BizTalk_Email_DistList_Audit_Delete, Line 1 [Batch Start Line 543]
There is already an object named 'Ch_BizTalk_Email_DistList_Audit_Delete' in the database.
Msg 2714, Level 16, State 2, Procedure Ch_BizTalk_Email_DistList_trgAfterUpdate, Line 1 [Batch Start Line 682]
There is already an object named 'Ch_BizTalk_Email_DistList_trgAfterUpdate' in the database.
Msg 3902, Level 16, State 1, Line 697
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Msg 2714, Level 16, State 5, Line 700
There is already an object named 'FK_Ch_BizTalk_Email_DistListCh_BizTalk_Email_Event' in the database.
Msg 1750, Level 16, State 1, Line 700
Could not create constraint or index. See previous errors.
Msg 3902, Level 16, State 1, Line 713
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

Thanks,
Neal

Getting this error even though I'm in a group that is db_owner.

Error is:
Cannot find the object 'pAutoAudit', because it does not exist or you do not have permission.

It created the stored proc as "Corp\myuserid.pAutoAudit"
I added two print statements to help debug:

-- This is the line of code causing the issue:
print Concat('@AuditSchema=', @AuditSchema)
SET @Sql = 'ALTER SCHEMA ' + quotename(@AuditSchema) + ' TRANSFER dbo.pAutoAudit'
print Concat('@Sql=', @Sql)
EXEC (@Sql)

Above shows:
@AuditSchema=Audit
@Sql=ALTER SCHEMA [Audit] TRANSFER dbo.pAutoAudit

Despite all that, I'm able to turn on AutoAudit for specific tables and it's working okay.
But now we have to move this to another environment, where I give it to a DBA in that environment to run.

Thanks

P.S. See some work I did here: https://stackoverflow.com/questions/72019509/alter-schema-transfer-fails-even-when-im-a-db-owner/72019914#72019914
I've been needing to fix this to get it to production.