February 19, 2018 at 9:21 pm
Hi,
This is a little hard to explain, but I'll have a go 🙂
We have several tables in 2 databases that currently don't have a DateLastModified column and a DateCreated column in them. I have created these columns and they will be populated by several SPs moving forward, here's my problem. I have created an Audit database with column names PK, DatabaseName, TableName, DateLastModified and DateCreated to be able to get populated by building triggers on each table to populate this information. I'm struggling to build the query. The examples I have found for the last two days are either over complicated or they don't do what I need. I've never written triggers before and clearly it's messing with me.
Can you please help. If you need more information please let me know.
Kris
February 20, 2018 at 4:30 am
Kris-155042 - Monday, February 19, 2018 9:21 PMHi,
This is a little hard to explain, but I'll have a go 🙂
We have several tables in 2 databases that currently don't have a DateLastModified column and a DateCreated column in them. I have created these columns and they will be populated by several SPs moving forward, here's my problem. I have created an Audit database with column names PK, DatabaseName, TableName, DateLastModified and DateCreated to be able to get populated by building triggers on each table to populate this information. I'm struggling to build the query. The examples I have found for the last two days are either over complicated or they don't do what I need. I've never written triggers before and clearly it's messing with me.
Can you please help. If you need more information please let me know.
http://www.dotnettricks.com/learn/sqlserver/after-trigger-instead-of-trigger-example
I'd suggest you to go thru the above and create a test tables and triggers on your test server DB and implement. All the required objects are already present there.
February 20, 2018 at 6:22 am
If you have to write out the audits in this fashion, triggers are the way to go. However, be very cautious using triggers. They can be extremely expensive operations since they occur in response to other operations. You may see blocking as multiple processes from multiple databases attempt to write cross database queries. You may be putting yourself into a very problematic situation.
If you need to track every statement run against a table, you might be better off setting up extended events to capture queries performing inserts & updates. This will also add load to the system, but a lot less than the proposed cross database triggers.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
February 20, 2018 at 7:18 am
Whatever you do, don't audit INSERTS. I'll be back to explain why after work tonight.
{EDIT} And don't use supposedly "portable" CLR triggers. They're horrible for performance because the INSERTED and DELETED tables have to be fully materialized.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2018 at 9:58 am
subramaniam.chandrasekar - Tuesday, February 20, 2018 4:30 AMhttp://www.dotnettricks.com/learn/sqlserver/after-trigger-instead-of-trigger-exampleI'd suggest you to go thru the above and create a test tables and triggers on your test server DB and implement. All the required objects are already present there.
If you use INSTEAD of triggers, note that if the definition of the table changed (adding or removing columns) you will need to change the trigger as well.
February 20, 2018 at 10:58 am
Short answer:
You want a dynamically-generated, static trigger. That sounds contradictory but it's not.
More detailed answer:
You must meet two critical conditions here:
1) the trigger must execute as quickly as possible.
2) the trigger code must be generated rather than written by hand.
When you consider those together, what you end up with is code that dynamically generates the trigger based on the current table definition (with control value(s) passed in as required). But the generated code is static, i.e., column names, data types, etc. are not looked up at run time, but at trigger create time.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
February 20, 2018 at 6:49 pm
Alternative options you can use are database audit or temporal tables.
https://docs.microsoft.com/en-us/sql/relational-databases/security/auditing/sql-server-audit-database-engine
https://docs.microsoft.com/en-us/sql/relational-databases/tables/temporal-tables
February 20, 2018 at 8:53 pm
Jeff Moden - Tuesday, February 20, 2018 7:18 AMWhatever you do, don't audit INSERTS. I'll be back to explain why after work tonight.{EDIT} And don't use supposedly "portable" CLR triggers. They're horrible for performance because the INSERTED and DELETED tables have to be fully materialized.
Ok... on the "Don't audit INSERTs" thing... here's why not.
Trigger Audits Everything
You have TableA and TableAudit. Both tables start out empty and your trigger audits everything including INSERTs. The trigger audits by capturing the full row.
1. Where is the original data?
The answer is, the original data is in both TableA and TableAudit. Do you really need two copies of something that hasn't changed?
2. Where is the latest data?
The answer is, the latest data is in both TableA and TableAudit (same rows as the original data because no update has occurred). Do you really need two copies of the latest data?
3. How much space does that single row occupy as a number of rows?
I already gave this away. Whole-row audits that audit INSERTs instantly double the disk space requirements, not to mention backups, restores, tape, time to do each, etc, etc.
Scenario 1 Conclusion.
Auditing INSERTs wastes the time it took to fire the trigger and save the row and immediately doubled all space and time requirements for the life of the database.
1. Where is the original row?
It's in the audit table a second time because the update cause the original INSERT into TableA to be copied to TableAudit, which is where the original INSERT was audited. You still have two copies of the original data.
2. Where's the latest data?
In TableA, where it belongs.
3. How much space does that single row occupy as a number of rows after it has been updated once?
The answer is 3 rows instead of just the 2 that should have been needed.
Scenario 2 Conclusion.
As before, auditing INSERTs wastes the time it took to fire the trigger and save the row and immediately doubled all space and time requirements for the life of the database. With the advent of just a single update, you now have 3 times the space requirements instead of just two; 1 for the original row and one for the modified row.
If you don't think so, compare what would happen to TableAudit if you ended up with 1TB of data in TableA.
You should also plan on partitioning the audit tables sooner than later because they can and usually will get quite large in a very short period of time. The partitioning isn't for performance... it's for index and backup management. You can seriously cut down on backup times if you make the older partitions READ_ONLY.
Remember... don't audit INSERTs. Only audit changes. It'll save time, space, resources, and money. And, it'll make Michael John only half as angry. 😀
--Jeff Moden
Change is inevitable... Change for the better is not.
February 20, 2018 at 9:10 pm
Now, I told you that story to tell you this one. Consider the links that Evgeny Garaev provided above. The cool part about the Temporal Tables is that they auto-magically include columns that support Type 6 SCDs (Slowly Changing Dimensions). AND... They only audit changes, not INSERTs. Please see the following article on SCDs.
https://en.wikipedia.org/wiki/Slowly_changing_dimension
The bad part is that they are not without caveats (no auditing system is, especially when schema changes take place). Another really good part is that auditors love Temporal Tables because they very difficult to tamper with. Like I said, though... caveats... make sure that you read ALL the links in the article that Evgeny Garaev provided the link for before you think of using them or things like the system automatically deleting your hard earned history just might take you by surprise.
--Jeff Moden
Change is inevitable... Change for the better is not.
February 21, 2018 at 3:11 pm
Jeff Moden - Tuesday, February 20, 2018 8:53 PMJeff Moden - Tuesday, February 20, 2018 7:18 AMWhatever you do, don't audit INSERTS. I'll be back to explain why after work tonight.{EDIT} And don't use supposedly "portable" CLR triggers. They're horrible for performance because the INSERTED and DELETED tables have to be fully materialized.
Ok... on the "Don't audit INSERTs" thing... here's why not.
Trigger Audits Everything
You have TableA and TableAudit. Both tables start out empty and your trigger audits everything including INSERTs. The trigger audits by capturing the full row.
Scenario 1: Inserted row that's never updated.
You do an INSERT of just one row into TableA and the trigger dutifully makes an exact copy of that row. Now imagine that single row isn't changed for the entire life of the database. With that, there are some questions to think about.1. Where is the original data?
The answer is, the original data is in both TableA and TableAudit. Do you really need two copies of something that hasn't changed?2. Where is the latest data?
The answer is, the latest data is in both TableA and TableAudit (same rows as the original data because no update has occurred). Do you really need two copies of the latest data?3. How much space does that single row occupy as a number of rows?
I already gave this away. Whole-row audits that audit INSERTs instantly double the disk space requirements, not to mention backups, restores, tape, time to do each, etc, etc.Scenario 1 Conclusion.
Auditing INSERTs wastes the time it took to fire the trigger and save the row and immediately doubled all space and time requirements for the life of the database.
Scenario 2: Inserted row is updated once in its lifetime.
You do an INSERT of just one row into TableA and the trigger dutifully makes an exact copy of that row. Sometime in the lifetime of that row, a single update is made. With that, there are some questions to thing about.1. Where is the original row?
It's in the audit table a second time because the update cause the original INSERT into TableA to be copied to TableAudit, which is where the original INSERT was audited. You still have two copies of the original data.2. Where's the latest data?
In TableA, where it belongs.3. How much space does that single row occupy as a number of rows after it has been updated once?
The answer is 3 rows instead of just the 2 that should have been needed.Scenario 2 Conclusion.
As before, auditing INSERTs wastes the time it took to fire the trigger and save the row and immediately doubled all space and time requirements for the life of the database. With the advent of just a single update, you now have 3 times the space requirements instead of just two; 1 for the original row and one for the modified row.
Audit Only the DELETED Logical Table
1. For Scenario 1, we'd only have the one copy of the row in TableA and nothing in TableAudit. Nothing else is required because the latest data is right where it belongs... in TableA. No extra effort needed to be made by a trigger and no doubling of space or resource usage has occurred.
2. For Scenario 2, we'd have a copy of the lasted data in TableA and the old original data in TableAudt... just like we're supposed to. Nothing else is required because the latest data is right where it belongs... in TableA AND the orginal row is right where it belongs... in TableAudit. No 3rd row was required or occurred.
WAIT! What About Historical Reporting of a Row?
Either learn how to use UNION ALL to get the latest from TableA and combine it with TableAudit or create a reporting view/iTVF. It's that simple. Call it by the same name as "TableA" with an extension on the name like "TableA_Rpt" or "TableA_Hist" or whatever makes the most sense to you. Just don't ever have your audit triggers put the latest data in TableAudit except for the DELETE of a row.
Overall Conclusion for Audit Triggers
.Auditing INSERTs (or the INSERTED logical trigger table) is a terribly wasteful idea that brings no value to the table or the audit system. It takes twice the time to accomplish, twice the resources everywhere it appears, and twice the time to do backups, restores, etc. Only audit UPDATEs and DELETEs, both of which can be accomplished by only auditing the DELETED logical table in triggers.If you don't think so, compare what would happen to TableAudit if you ended up with 1TB of data in TableA.
You should also plan on partitioning the audit tables sooner than later because they can and usually will get quite large in a very short period of time. The partitioning isn't for performance... it's for index and backup management. You can seriously cut down on backup times if you make the older partitions READ_ONLY.
Remember... don't audit INSERTs. Only audit changes. It'll save time, space, resources, and money. And, it'll make Michael John only half as angry. 😀
Huh. Thanks Jeff, once again I am off to copy/paste your insight into an email, ask for approval and make some changes.
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
February 21, 2018 at 3:37 pm
Kris-155042 - Monday, February 19, 2018 9:21 PMHi,
This is a little hard to explain, but I'll have a go 🙂
We have several tables in 2 databases that currently don't have a DateLastModified column and a DateCreated column in them. I have created these columns and they will be populated by several SPs moving forward, here's my problem. I have created an Audit database with column names PK, DatabaseName, TableName, DateLastModified and DateCreated to be able to get populated by building triggers on each table to populate this information. I'm struggling to build the query. The examples I have found for the last two days are either over complicated or they don't do what I need. I've never written triggers before and clearly it's messing with me.
Can you please help. If you need more information please let me know.
>> ... have several tables in 2 databases that currently don't have a DateLastModified column and a DateCreated column in them. I have created these columns and they will be populated by several SPs moving forward, here's my problem. <<
You're exactly right; that is your problem. Mixing data and metadata doesn't work in RDBMS. Just think about happens to a to a row that is deleted, by intention, sabotage or accident. Your audit did for trail is destroyed exclamation this sort of crap is like keeping an extra set of car keys in your glove compartment so you can't be locked out, or actually more like putting the title to your car along with all your insurance information in the glove compartment, so the car burns up in an accident everything is destroyed.
This is why we use third-party audit software. It forms a barrier around the entire database, and gives you "border guards" that log everything coming in and going out across that border. The reason this security model is required by law. In many cases is that the audit data can be physically separated from the data that it audits.
Some of the many other reasons we hate using triggers are :(1) the overhead of triggers (2) triggers are procedural code and this is a declarative language (3) trigger syntax varies hugely from product to product, so the audit model cannot port (4) . Security; if the audit information is in the table being audited, and you can not to keep people who have access to that table from also having access to the audit data (this is why you can go to jail). (5) if you buy an outside third party audit tool, then you have reduced your ROI (risk of incarceration; the vendor then has a lot of legal responsibility that you really don't want to have)
Please post DDL and follow ANSI/ISO standards when asking for help.
February 21, 2018 at 3:52 pm
jcelko212 32090 - Wednesday, February 21, 2018 3:37 PM(2) triggers are procedural code and this is a declarative language
(3) trigger syntax varies hugely from product to product, so the audit model cannot port (4).
So what... True portability is a myth. Note that's not even a rhetorical question... it's a statement.
Security; if the audit information is in the table being audited, and you can not to keep people who have access to that table from also having access to the audit data (this is why you can go to jail).
Finally... a real truth.
(5) if you buy an outside third party audit tool, then you have reduced your ROI (risk of incarceration; the vendor then has a lot of legal responsibility that you really don't want to have)
Caveate Emptor. The vendor may have shared legal responsibility but what happens to your data is ultimately your responsibility and whether you want to have it or not, you still have a huge amount of legal responsibility. If you don't think so, post your SSN and let's find out. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply