0

I have a custom .NET CLR trigger that is hooked up to my database to handle slightly more advance insert, update, delete triggers. Basically it is tying in some data not readily available to the database such as workstation ID and user name. I do not have access to the source code as it was written many years ago by some other developer. We are rolling out a second instance of our software package and database. The new database is 2019 whereas the existing database is 2016.

I am running into an issue where on some tables the trigger is working exactly as expected and on some it is not capturing the inserts/updates/deletes into our audit table.

I have ensured that CLR is enabled on the database but that is the only troubleshooting step I was able to come up with.

Edit, not sure why I didn't try this before but when doing an insert from ssms I get this error:

Error

1

Edit2: This is fixed. I was able to use dotPeek to look at the source code (we own it) and determine that it was trying to do a selct * from INFORMATION_SCHEMA.Tables and the new database had a bunch of tSQLt items that conflicted with the code. After disabling Unit Testing with EXEC tSQLt.Uninstall the CLR trigger works as expected. Thanks!

Improve this question
8
  • Which dbms are you using? Commented Feb 24, 2023 at 13:25
  • MSSQL Standard. I've got some error code that I am going to add to the original post. That my help. Commented Feb 24, 2023 at 13:52
  • Without documentation or source code you're really going to be flying blind. Is it really not committed to Git or some other source code control? Perhaps there's deployment of configuration data required to make it work. Does the working database contain something named Private_NullCellTable in sys.objects? Does the failing database? Commented Feb 24, 2023 at 15:42
  • Unfortunately, I cannot find the source code anywhere. I am still working on trying to dig that up but have not had any luck as of yet. I am not seeing anything with the name Private_NullCellTable in either database. Commented Feb 24, 2023 at 16:06
  • The error text would be more useful than a picture of it, but if I squint really hard, I see something called DetermineTableNameFromSchema. I'm making a guess here based on that name, but it seems like there's something in the trigger's code that dynamically figures out a table name to do something with. Commented Feb 24, 2023 at 18:20
Related questions
78
How to Generate Scripts For All Triggers in Database Using Microsoft SQL Server Management Studio
0
Database audit trigger for username
1
How to compare old and new field values in a CLR insert and update trigger
Load 6 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.