Triggers Tips

I sometimes want to perform auditing or other actions in a trigger based on some criteria. More specifically, there are a few cases that may warrant an e-mail; for example, if a web sale takes place that requires custom or overnight shipping and handling. It is tempting to just add code to the trigger that sends an e-mail when these criteria are met. But this can be problematic for two reasons: (1) your users are waiting for that processing to occur, and (2) if you can't send the e-mail, how do you decide whether or not to roll back the transaction, and how do you bring the problem to the attention of the administrator?

We were just working through a deployment and ran into a snag which caused us to run over the allocated amount of down time. To speed up the some of the upcoming steps in the process we dropped a few triggers on our larger tables. We did this to prevent the large number of inserts into the audit tables from extending the down time even further. Not having the trigger fire for this process was something we were willing to sacrifice. Unfortunately, once we released the system to production we no longer had the triggers and had to conduct a manual review to ensure we had all of our triggers. Missing the data once we went to production was a major issue on top of having to extend the down time. Do you have any suggestions...

I have a trigger created on a table which will fire when either an INSERT, DELETE or UPDATE statements are executed against the table. I want to suppress the trigger from firing for a particular statement while it remains in its normal execution state for any of the other statements. Is there any way this can be done dynamically?

I have two triggers defined on my table which are set to fire on the same table actions (i.e. an INSERT, DELETE, UPDATE transaction). The second trigger that fires is dependent on the first fired trigger. How can I make sure that they fire in the correct order to enforce my business logic? In addition, as our system changes, what are some of the caveats that I need to be aware of when managing the trigger firing order?

I am trying to resolve an issue with triggers. We use them for things like automatically setting the update date and time on some tables. Recently we added a second trigger to a table and now we are getting the error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)". For the time being we dropped that latest trigger. Can you help us to resolve this?

Performing DML operations through tested application are always safe and efficient. This process also prevents an accidental update or delete of all rows in a table. In my environment, I have a couple of tables that often require ad-hoc updates of certain values or delete of certain rows. On our development SQL Servers ad-hoc DML operations for various purposes are frequent. Unfortunately, there have been a couple of incidents in when someone accidentally issued a DML command without a WHERE clause, hence affecting the entire table. Is there an efficient way to prevent accidental DML operations without a WHERE clause?

I have processes in my applications that I need to audit the data and write the data out to a specific audit table, but only in specific circumstances based on business logic and not for every transaction. I know triggers are available on a per table basis to audit the data, but I do not want to audit all of the INSERT, UPDATE or DELETE statements that run from a variety of applications (fat clients, web clients, automated processes, monthly batch processes, etc). How can I audit only the specific processes versus all transactions that run? Are any elegant options available directly with the INSERT, UPDATE or DELETE statements?

I'm trying to put triggers on an existing database, but I am having a hard time understanding how to use the inserted and deleted tables. They make sense when I'm dealing with an INSERT or DELETE operation, but how about an update? And do they contain a single row per row of data changed or are they truly tables? In this tip we take a closer look at these these tables.

We were just working through a deployment and ran into a snag which caused us to run over the allocated amount of down time. To speed up the some of the upcoming steps in the process we dropped a few triggers on our larger tables. We did this to prevent the large number of inserts into the audit tables from extending the down time even further. Not having the trigger fire for this process was something we were willing to sacrifice. Unfortunately, once we released the system to production we no longer had the triggers and had to conduct a manual review to ensure we had all of our triggers. Missing the data once we went to production was a major issue on top of having to extend the down time. Do you have any suggestions...

I am trying to resolve an issue with triggers. We use them for things like automatically setting the update date and time on some tables. Recently we added a second trigger to a table and now we are getting the error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)". For the time being we dropped that latest trigger. Can you help us to resolve this?

I have two triggers defined on my table which are set to fire on the same table actions (i.e. an INSERT, DELETE, UPDATE transaction). The second trigger that fires is dependent on the first fired trigger. How can I make sure that they fire in the correct order to enforce my business logic? In addition, as our system changes, what are some of the caveats that I need to be aware of when managing the trigger firing order?

I have a trigger created on a table which will fire when either an INSERT, DELETE or UPDATE statements are executed against the table. I want to suppress the trigger from firing for a particular statement while it remains in its normal execution state for any of the other statements. Is there any way this can be done dynamically?

Performing DML operations through tested application are always safe and efficient. This process also prevents an accidental update or delete of all rows in a table. In my environment, I have a couple of tables that often require ad-hoc updates of certain values or delete of certain rows. On our development SQL Servers ad-hoc DML operations for various purposes are frequent. Unfortunately, there have been a couple of incidents in when someone accidentally issued a DML command without a WHERE clause, hence affecting the entire table. Is there an efficient way to prevent accidental DML operations without a WHERE clause?

I'm trying to put triggers on an existing database, but I am having a hard time understanding how to use the inserted and deleted tables. They make sense when I'm dealing with an INSERT or DELETE operation, but how about an update? And do they contain a single row per row of data changed or are they truly tables? In this tip we take a closer look at these these tables.

I sometimes want to perform auditing or other actions in a trigger based on some criteria. More specifically, there are a few cases that may warrant an e-mail; for example, if a web sale takes place that requires custom or overnight shipping and handling. It is tempting to just add code to the trigger that sends an e-mail when these criteria are met. But this can be problematic for two reasons: (1) your users are waiting for that processing to occur, and (2) if you can't send the e-mail, how do you decide whether or not to roll back the transaction, and how do you bring the problem to the attention of the administrator?

I have processes in my applications that I need to audit the data and write the data out to a specific audit table, but only in specific circumstances based on business logic and not for every transaction. I know triggers are available on a per table basis to audit the data, but I do not want to audit all of the INSERT, UPDATE or DELETE statements that run from a variety of applications (fat clients, web clients, automated processes, monthly batch processes, etc). How can I audit only the specific processes versus all transactions that run? Are any elegant options available directly with the INSERT, UPDATE or DELETE statements?

I'm trying to put triggers on an existing database, but I am having a hard time understanding how to use the inserted and deleted tables. They make sense when I'm dealing with an INSERT or DELETE operation, but how about an update? And do they contain a single row per row of data changed or are they truly tables? In this tip we take a closer look at these these tables.

I sometimes want to perform auditing or other actions in a trigger based on some criteria. More specifically, there are a few cases that may warrant an e-mail; for example, if a web sale takes place that requires custom or overnight shipping and handling. It is tempting to just add code to the trigger that sends an e-mail when these criteria are met. But this can be problematic for two reasons: (1) your users are waiting for that processing to occur, and (2) if you can't send the e-mail, how do you decide whether or not to roll back the transaction, and how do you bring the problem to the attention of the administrator?

Performing DML operations through tested application are always safe and efficient. This process also prevents an accidental update or delete of all rows in a table. In my environment, I have a couple of tables that often require ad-hoc updates of certain values or delete of certain rows. On our development SQL Servers ad-hoc DML operations for various purposes are frequent. Unfortunately, there have been a couple of incidents in when someone accidentally issued a DML command without a WHERE clause, hence affecting the entire table. Is there an efficient way to prevent accidental DML operations without a WHERE clause?

I am trying to resolve an issue with triggers. We use them for things like automatically setting the update date and time on some tables. Recently we added a second trigger to a table and now we are getting the error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)". For the time being we dropped that latest trigger. Can you help us to resolve this?

I have a trigger created on a table which will fire when either an INSERT, DELETE or UPDATE statements are executed against the table. I want to suppress the trigger from firing for a particular statement while it remains in its normal execution state for any of the other statements. Is there any way this can be done dynamically?

I have two triggers defined on my table which are set to fire on the same table actions (i.e. an INSERT, DELETE, UPDATE transaction). The second trigger that fires is dependent on the first fired trigger. How can I make sure that they fire in the correct order to enforce my business logic? In addition, as our system changes, what are some of the caveats that I need to be aware of when managing the trigger firing order?

I have processes in my applications that I need to audit the data and write the data out to a specific audit table, but only in specific circumstances based on business logic and not for every transaction. I know triggers are available on a per table basis to audit the data, but I do not want to audit all of the INSERT, UPDATE or DELETE statements that run from a variety of applications (fat clients, web clients, automated processes, monthly batch processes, etc). How can I audit only the specific processes versus all transactions that run? Are any elegant options available directly with the INSERT, UPDATE or DELETE statements?

We were just working through a deployment and ran into a snag which caused us to run over the allocated amount of down time. To speed up the some of the upcoming steps in the process we dropped a few triggers on our larger tables. We did this to prevent the large number of inserts into the audit tables from extending the down time even further. Not having the trigger fire for this process was something we were willing to sacrifice. Unfortunately, once we released the system to production we no longer had the triggers and had to conduct a manual review to ensure we had all of our triggers. Missing the data once we went to production was a major issue on top of having to extend the down time. Do you have any suggestions...