August 15, 2016 at 6:13 am
Hi experts,
I need a trigger script that will insert a record in Audit table when performing update ,delete operation in a that table.It must handle this by using temp table concept instead of variables
Note :When performing DML operations(update,delete) by using begin tran and rollback tran .It must not get affected .Thanks in advance
August 15, 2016 at 6:29 am
Satiz (8/15/2016)
Hi experts,I need a trigger script that will insert a record in Audit table when performing update ,delete operation in a that table.It must handle this by using temp table concept instead of variables
Note :When performing DML operations(update,delete) by using begin tran and rollback tran .It must not get affected .Thanks in advance
If that's all you want to do, you shouldn't need a temp table or variables. You can use the INSERTED and DELETED tables. See https://msdn.microsoft.com/en-us/library/ms191300.aspx.
August 15, 2016 at 7:08 am
Also, you should probably avoid auditing inserts. It's an unnecessary duplication of data because even if there's never an update, the original data lives in the original table. Only audit updates and deletes and then only audit the DELETED logical tables in triggers. The latest data will ALWAYS be available in the original table unless it was an actual delete, which IS audited.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2016 at 10:26 am
Jeff Moden (8/15/2016)
Also, you should probably avoid auditing inserts. It's an unnecessary duplication of data because even if there's never an update, the original data lives in the original table. Only audit updates and deletes and then only audit the DELETED logical tables in triggers. The latest data will ALWAYS be available in the original table unless it was an actual delete, which IS audited.
But that requires assuming the auditors have access to the original table. Sometimes they don't; indeed, often auditing rules require that they not have access.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 15, 2016 at 1:14 pm
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
Also, you should probably avoid auditing inserts. It's an unnecessary duplication of data because even if there's never an update, the original data lives in the original table. Only audit updates and deletes and then only audit the DELETED logical tables in triggers. The latest data will ALWAYS be available in the original table unless it was an actual delete, which IS audited.But that requires assuming the auditors have access to the original table. Sometimes they don't; indeed, often auditing rules require that they not have access.
Or that the person writing the query for the auditors knows how to include data from both tables.
August 15, 2016 at 3:27 pm
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
Also, you should probably avoid auditing inserts. It's an unnecessary duplication of data because even if there's never an update, the original data lives in the original table. Only audit updates and deletes and then only audit the DELETED logical tables in triggers. The latest data will ALWAYS be available in the original table unless it was an actual delete, which IS audited.But that requires assuming the auditors have access to the original table. Sometimes they don't; indeed, often auditing rules require that they not have access.
True enough but that's why there are such things as views. I really hate it when an audit table literally doubles or triples the size of the data in a mere 500GB table. If the auditors want proof that the view is correct, they can come see me because the users that need to build reports would also be using the same view. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2016 at 3:28 pm
Ed Wagner (8/15/2016)
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
Also, you should probably avoid auditing inserts. It's an unnecessary duplication of data because even if there's never an update, the original data lives in the original table. Only audit updates and deletes and then only audit the DELETED logical tables in triggers. The latest data will ALWAYS be available in the original table unless it was an actual delete, which IS audited.But that requires assuming the auditors have access to the original table. Sometimes they don't; indeed, often auditing rules require that they not have access.
Or that the person writing the query for the auditors knows how to include data from both tables.
Heh... see above. 🙂 We think more alike that I could have ever imagined. You even like 3 Sloppy Joes. 😉
--Jeff Moden
Change is inevitable... Change for the better is not.
August 15, 2016 at 3:53 pm
Jeff Moden (8/15/2016)
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
Also, you should probably avoid auditing inserts. It's an unnecessary duplication of data because even if there's never an update, the original data lives in the original table. Only audit updates and deletes and then only audit the DELETED logical tables in triggers. The latest data will ALWAYS be available in the original table unless it was an actual delete, which IS audited.But that requires assuming the auditors have access to the original table. Sometimes they don't; indeed, often auditing rules require that they not have access.
True enough but that's why there are such things as views. I really hate it when an audit table literally doubles or triples the size of the data in a mere 500GB table. If the auditors want proof that the view is correct, they can come see me because the users that need to build reports would also be using the same view. 🙂
In this case, a view wouldn't help, because they would be explicitly denied access to the main table. Otherwise, at least in theory, auditors could "cheat", which would not be good. Genuine auditing can have very strict rules.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 15, 2016 at 4:15 pm
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
Also, you should probably avoid auditing inserts. It's an unnecessary duplication of data because even if there's never an update, the original data lives in the original table. Only audit updates and deletes and then only audit the DELETED logical tables in triggers. The latest data will ALWAYS be available in the original table unless it was an actual delete, which IS audited.But that requires assuming the auditors have access to the original table. Sometimes they don't; indeed, often auditing rules require that they not have access.
True enough but that's why there are such things as views. I really hate it when an audit table literally doubles or triples the size of the data in a mere 500GB table. If the auditors want proof that the view is correct, they can come see me because the users that need to build reports would also be using the same view. 🙂
In this case, a view wouldn't help, because they would be explicitly denied access to the main table. Otherwise, at least in theory, auditors could "cheat", which would not be good. Genuine auditing can have very strict rules.
At least they would applaud my security according to their very strict rules. No unauthorized person can see the latest data. 😀
I have also, in the past, retrained a couple of auditors in the areas of common sense and logic. Unnecessary duplication of data is one of those things that also have some strict rules. If you audit both the INSERTED and DELETED logical tables in a trigger, you've essentially tripled the size of the database which requires more disk space and tapes to be encrypted, etc, etc. The auditors that I've worked with so far note the exception in my favor because of the reduction of duplicated data.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 16, 2016 at 8:23 am
Jeff Moden (8/15/2016)
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
Also, you should probably avoid auditing inserts. It's an unnecessary duplication of data because even if there's never an update, the original data lives in the original table. Only audit updates and deletes and then only audit the DELETED logical tables in triggers. The latest data will ALWAYS be available in the original table unless it was an actual delete, which IS audited.But that requires assuming the auditors have access to the original table. Sometimes they don't; indeed, often auditing rules require that they not have access.
True enough but that's why there are such things as views. I really hate it when an audit table literally doubles or triples the size of the data in a mere 500GB table. If the auditors want proof that the view is correct, they can come see me because the users that need to build reports would also be using the same view. 🙂
In this case, a view wouldn't help, because they would be explicitly denied access to the main table. Otherwise, at least in theory, auditors could "cheat", which would not be good. Genuine auditing can have very strict rules.
At least they would applaud my security according to their very strict rules. No unauthorized person can see the latest data. 😀
I have also, in the past, retrained a couple of auditors in the areas of common sense and logic. Unnecessary duplication of data is one of those things that also have some strict rules. If you audit both the INSERTED and DELETED logical tables in a trigger, you've essentially tripled the size of the database which requires more disk space and tapes to be encrypted, etc, etc. The auditors that I've worked with so far note the exception in my favor because of the reduction of duplicated data.
But then it's no longer considered a valid audit. I worked at a place that manufactured surgical implants for humans, and the auditing rules were very strict. Any violation of those rules could cause your FDA license for that product to be pulled. So check the rules because you just assume you'll have access to other data.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 16, 2016 at 8:31 am
ScottPletcher (8/16/2016)
Jeff Moden (8/15/2016)
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
Also, you should probably avoid auditing inserts. It's an unnecessary duplication of data because even if there's never an update, the original data lives in the original table. Only audit updates and deletes and then only audit the DELETED logical tables in triggers. The latest data will ALWAYS be available in the original table unless it was an actual delete, which IS audited.But that requires assuming the auditors have access to the original table. Sometimes they don't; indeed, often auditing rules require that they not have access.
True enough but that's why there are such things as views. I really hate it when an audit table literally doubles or triples the size of the data in a mere 500GB table. If the auditors want proof that the view is correct, they can come see me because the users that need to build reports would also be using the same view. 🙂
In this case, a view wouldn't help, because they would be explicitly denied access to the main table. Otherwise, at least in theory, auditors could "cheat", which would not be good. Genuine auditing can have very strict rules.
At least they would applaud my security according to their very strict rules. No unauthorized person can see the latest data. 😀
I have also, in the past, retrained a couple of auditors in the areas of common sense and logic. Unnecessary duplication of data is one of those things that also have some strict rules. If you audit both the INSERTED and DELETED logical tables in a trigger, you've essentially tripled the size of the database which requires more disk space and tapes to be encrypted, etc, etc. The auditors that I've worked with so far note the exception in my favor because of the reduction of duplicated data.
But then it's no longer considered a valid audit. I worked at a place that manufactured surgical implants for humans, and the auditing rules were very strict. Any violation of those rules could cause your FDA license for that product to be pulled. So check the rules because you just assume you'll have access to other data.
Someone still has to write the query to pull the data for the auditors. I would hope that the person who writes it would know their system and also what they are doing. Then someone has to run the query to pull the data. To run the query, they would require permission to run it.
The bottom line is that someone has to have permission to the data to be able to query it. If nobody has permission to it, then the audit can't be performed. Whoever that person is, it is certainly not the auditors.
August 16, 2016 at 9:34 am
Ed Wagner (8/16/2016)
ScottPletcher (8/16/2016)
Jeff Moden (8/15/2016)
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
Also, you should probably avoid auditing inserts. It's an unnecessary duplication of data because even if there's never an update, the original data lives in the original table. Only audit updates and deletes and then only audit the DELETED logical tables in triggers. The latest data will ALWAYS be available in the original table unless it was an actual delete, which IS audited.But that requires assuming the auditors have access to the original table. Sometimes they don't; indeed, often auditing rules require that they not have access.
True enough but that's why there are such things as views. I really hate it when an audit table literally doubles or triples the size of the data in a mere 500GB table. If the auditors want proof that the view is correct, they can come see me because the users that need to build reports would also be using the same view. 🙂
In this case, a view wouldn't help, because they would be explicitly denied access to the main table. Otherwise, at least in theory, auditors could "cheat", which would not be good. Genuine auditing can have very strict rules.
At least they would applaud my security according to their very strict rules. No unauthorized person can see the latest data. 😀
I have also, in the past, retrained a couple of auditors in the areas of common sense and logic. Unnecessary duplication of data is one of those things that also have some strict rules. If you audit both the INSERTED and DELETED logical tables in a trigger, you've essentially tripled the size of the database which requires more disk space and tapes to be encrypted, etc, etc. The auditors that I've worked with so far note the exception in my favor because of the reduction of duplicated data.
But then it's no longer considered a valid audit. I worked at a place that manufactured surgical implants for humans, and the auditing rules were very strict. Any violation of those rules could cause your FDA license for that product to be pulled. So check the rules because you just assume you'll have access to other data.
Someone still has to write the query to pull the data for the auditors. I would hope that the person who writes it would know their system and also what they are doing. Then someone has to run the query to pull the data. To run the query, they would require permission to run it.
The bottom line is that someone has to have permission to the data to be able to query it. If nobody has permission to it, then the audit can't be performed. Whoever that person is, it is certainly not the auditors.
Auditors would have access to the historical audit trail, but not to current data. Formal audits are much more restrictive than "self audits" or what are really audits-in-name-only, where someone within the company "audits" the data.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 16, 2016 at 9:42 am
ScottPletcher (8/16/2016)
Ed Wagner (8/16/2016)
ScottPletcher (8/16/2016)
Jeff Moden (8/15/2016)
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
Also, you should probably avoid auditing inserts. It's an unnecessary duplication of data because even if there's never an update, the original data lives in the original table. Only audit updates and deletes and then only audit the DELETED logical tables in triggers. The latest data will ALWAYS be available in the original table unless it was an actual delete, which IS audited.But that requires assuming the auditors have access to the original table. Sometimes they don't; indeed, often auditing rules require that they not have access.
True enough but that's why there are such things as views. I really hate it when an audit table literally doubles or triples the size of the data in a mere 500GB table. If the auditors want proof that the view is correct, they can come see me because the users that need to build reports would also be using the same view. 🙂
In this case, a view wouldn't help, because they would be explicitly denied access to the main table. Otherwise, at least in theory, auditors could "cheat", which would not be good. Genuine auditing can have very strict rules.
At least they would applaud my security according to their very strict rules. No unauthorized person can see the latest data. 😀
I have also, in the past, retrained a couple of auditors in the areas of common sense and logic. Unnecessary duplication of data is one of those things that also have some strict rules. If you audit both the INSERTED and DELETED logical tables in a trigger, you've essentially tripled the size of the database which requires more disk space and tapes to be encrypted, etc, etc. The auditors that I've worked with so far note the exception in my favor because of the reduction of duplicated data.
But then it's no longer considered a valid audit. I worked at a place that manufactured surgical implants for humans, and the auditing rules were very strict. Any violation of those rules could cause your FDA license for that product to be pulled. So check the rules because you just assume you'll have access to other data.
Someone still has to write the query to pull the data for the auditors. I would hope that the person who writes it would know their system and also what they are doing. Then someone has to run the query to pull the data. To run the query, they would require permission to run it.
The bottom line is that someone has to have permission to the data to be able to query it. If nobody has permission to it, then the audit can't be performed. Whoever that person is, it is certainly not the auditors.
Auditors would have access to the historical audit trail, but not to current data. Formal audits are much more restrictive than "self audits" or what are really audits-in-name-only, where someone within the company "audits" the data.
I've participated in several different types of audits - ISO/QS/TE 9000, ISO 27001 and Sarbanes-Oxley. I've never seen a case where the auditors had access to the database. The data has been queried for external auditors and they did the auditing. It may very well be the case that audits exist where the auditors have permission to the database, but I've never seen one.
August 16, 2016 at 10:05 am
Ed Wagner (8/16/2016)
ScottPletcher (8/16/2016)
Ed Wagner (8/16/2016)
ScottPletcher (8/16/2016)
Jeff Moden (8/15/2016)
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
ScottPletcher (8/15/2016)
Jeff Moden (8/15/2016)
Also, you should probably avoid auditing inserts. It's an unnecessary duplication of data because even if there's never an update, the original data lives in the original table. Only audit updates and deletes and then only audit the DELETED logical tables in triggers. The latest data will ALWAYS be available in the original table unless it was an actual delete, which IS audited.But that requires assuming the auditors have access to the original table. Sometimes they don't; indeed, often auditing rules require that they not have access.
True enough but that's why there are such things as views. I really hate it when an audit table literally doubles or triples the size of the data in a mere 500GB table. If the auditors want proof that the view is correct, they can come see me because the users that need to build reports would also be using the same view. 🙂
In this case, a view wouldn't help, because they would be explicitly denied access to the main table. Otherwise, at least in theory, auditors could "cheat", which would not be good. Genuine auditing can have very strict rules.
At least they would applaud my security according to their very strict rules. No unauthorized person can see the latest data. 😀
I have also, in the past, retrained a couple of auditors in the areas of common sense and logic. Unnecessary duplication of data is one of those things that also have some strict rules. If you audit both the INSERTED and DELETED logical tables in a trigger, you've essentially tripled the size of the database which requires more disk space and tapes to be encrypted, etc, etc. The auditors that I've worked with so far note the exception in my favor because of the reduction of duplicated data.
But then it's no longer considered a valid audit. I worked at a place that manufactured surgical implants for humans, and the auditing rules were very strict. Any violation of those rules could cause your FDA license for that product to be pulled. So check the rules because you just assume you'll have access to other data.
Someone still has to write the query to pull the data for the auditors. I would hope that the person who writes it would know their system and also what they are doing. Then someone has to run the query to pull the data. To run the query, they would require permission to run it.
The bottom line is that someone has to have permission to the data to be able to query it. If nobody has permission to it, then the audit can't be performed. Whoever that person is, it is certainly not the auditors.
Auditors would have access to the historical audit trail, but not to current data. Formal audits are much more restrictive than "self audits" or what are really audits-in-name-only, where someone within the company "audits" the data.
I've participated in several different types of audits - ISO/QS/TE 9000, ISO 27001 and Sarbanes-Oxley. I've never seen a case where the auditors had access to the database. The data has been queried for external auditors and they did the auditing. It may very well be the case that audits exist where the auditors have permission to the database, but I've never seen one.
That is my point. They cannot access the current table. They can only access the audit trail, which is usually provided outside of the current db; I suppose it could be in the actual db, and they just can't access anything else. In a true audit, auditors often wouldn't even know the name of the actual db, where that instance physically resides, etc.. The only thing they see is the auditing data they need to do the audit.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
August 16, 2016 at 10:10 am
Even so, it's likely that for in-house audits, or less formal audits, the current table could be freely accessed, and then INSERTs might not need logged. But verify that before you exclude them, because obviously it would be a royal pain to try to add them in later!
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 15 posts - 1 through 15 (of 24 total)
You must be logged in to reply to this topic. Login to reply