Help with Trigger - nesting level exceeded
-
I am new to writing Triggers, so please forgive me if this is rudimentory. In fact, these are the first triggers I have written, ever.
Here is one of two triggers I need on my table. This one simply puts the last modified date (or date inserted if that be the case) and the user who modified/updated the record:
CREATE TRIGGER trigger1 ON MyTable AFTER INSERT, UPDATE
AS
IF @@ROWCOUNT = 0
RETURN
UPDATE MyTable
SET
LastUpdateDate = GETDATE(),
LastUpdateUser = SUSER_SNAME()
FROM
inserted
JOIN
MyTable ON MyTable.DOCID = inserted.DOCID
Here is the other trigger. This one puts a Y or N in Col05 depending on what is in Col04:
CREATE TRIGGER trigger2 ON MyTable AFTER INSERT, UPDATE
AS
IF @@ROWCOUNT = 0
RETURN
UPDATE MyTable
SET
Col05 = CASE WHEN MyTable.Col04 LIKE '155%' THEN 'Y' ELSE 'N' END
FROM
inserted
JOIN
MyTable ON MyTable.DOCID = inserted.DOCID
My problem has something to do with nesting, or recursive triggers, or something???? When a record is inserted or updated in MyTable, I receive an error saying "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)."
I want both triggers to fire on insert/update. I've tried using sp_settriggerorder to make trigger1 fire first. And I've tried inserting *IF TRIGGER_NESTLEVEL() > 1 RETURN* at beginning of trigger1. This eliminates the error, however, when trigger2 fires, the last modified date/user does not get updated (it skips trigger1??).
Can someone tell me how to use both triggers on my table and eliminate the error AND to have both triggers update my table?
Thank you.
Joel
-
Your trigger TRIGGER1 on MyTable self-triggers!
Please use UPDATE() function and exclude columns LastUpdateDate and LastUpdateUser.
Alternatively, set these columns with default values GETDATE() and SUSER_SNAME() and use trigger only when updating.
Books Online says
CREATE TRIGGER
Creates a trigger, which is a special kind of stored procedure that executes automatically when a user attempts the specified data-modification statement on the specified table. Microsoft® SQL Server™ allows the creation of multiple triggers for any given INSERT, UPDATE, or DELETE statement.
Syntax
CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
{ { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ { IF UPDATE ( column )
[ { AND | OR } UPDATE ( column ) ]
[ ...n ]
| IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
{ comparison_operator } column_bitmask [ ...n ]
} ]
sql_statement [ ...n ]
}
}
Arguments
trigger_name
Is the name of the trigger. A trigger name must conform to the rules for identifiers and must be unique within the database. Specifying the trigger owner name is optional.
Table | view
Is the table or view on which the trigger is executed and is sometimes called the trigger table or trigger view. Specifying the owner name of the table or view is optional.
WITH ENCRYPTION
Encrypts the syscomments entries that contain the text of CREATE TRIGGER. Using WITH ENCRYPTION prevents the trigger from being published as part of SQL Server replication.
AFTER
Specifies that the trigger is fired only when all operations specified in the triggering SQL statement have executed successfully. All referential cascade actions and constraint checks also must succeed before this trigger executes.
AFTER is the default, if FOR is the only keyword specified.
AFTER triggers cannot be defined on views.
INSTEAD OF
Specifies that the trigger is executed instead of the triggering SQL statement, thus overriding the actions of the triggering statements.
At most, one INSTEAD OF trigger per INSERT, UPDATE, or DELETE statement can be defined on a table or view. However, it is possible to define views on views where each view has its own INSTEAD OF trigger.
INSTEAD OF triggers are not allowed on updateable views WITH CHECK OPTION. SQL Server will raise an error if an INSTEAD OF trigger is added to an updateable view WITH CHECK OPTION specified. The user must remove that option using ALTER VIEW before defining the INSTEAD OF trigger.
{ [DELETE] [,] [INSERT] [,] [UPDATE] }
Are keywords that specify which data modification statements, when attempted against this table or view, activate the trigger. At least one option must be specified. Any combination of these in any order is allowed in the trigger definition. If more than one option is specified, separate the options with commas.
For INSTEAD OF triggers, the DELETE option is not allowed on tables that have a referential relationship specifying a cascade action ON DELETE. Similarly, the UPDATE option is not allowed on tables that have a referential relationship specifying a cascade action ON UPDATE.
WITH APPEND
Specifies that an additional trigger of an existing type should be added. Use of this optional clause is needed only when the compatibility level is 65 or lower. If the compatibility level is 70 or higher, the WITH APPEND clause is not needed to add an additional trigger of an existing type (this is the default behavior of CREATE TRIGGER with the compatibility level setting of 70 or higher.) For more information, see sp_dbcmptlevel.
WITH APPEND cannot be used with INSTEAD OF triggers or if AFTER trigger is explicitly stated. WITH APPEND can be used only when FOR is specified (without INSTEAD OF or AFTER) for backward compatibility reasons. WITH APPEND and FOR (which is interpreted as AFTER) will not be supported in future releases.
NOT FOR REPLICATION
Indicates that the trigger should not be executed when a replication process modifies the table involved in the trigger.
AS
Are the actions the trigger is to perform.
sql_statement
Is the trigger condition(s) and action(s). Trigger conditions specify additional criteria that determine whether the attempted DELETE, INSERT, or UPDATE statements cause the trigger action(s) to be carried out.
The trigger actions specified in the Transact-SQL statements go into effect when the DELETE, INSERT, or UPDATE operation is attempted.
Triggers can include any number and kind of Transact-SQL statements. A trigger is designed to check or change data based on a data modification statement; it should not return data to the user. The Transact-SQL statements in a trigger often include control-of-flow language. A few special tables are used in CREATE TRIGGER statements:
- deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use:
SELECT *FROM deleted - In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ntext, or image column references in the inserted and deleted tables if the compatibility level is equal to 70. The text, ntext, and image values in the inserted and deleted tables cannot be accessed. To retrieve the new value in either an INSERT or UPDATE trigger, join the inserted table with the original update table. When the compatibility level is 65 or lower, null values are returned for inserted or deleted text, ntext, or image columns that allow null values; zero-length strings are returned if the columns are not nullable.
If the compatibility level is 80 or higher, SQL Server allows the update of text, ntext, or image columns through the INSTEAD OF trigger on tables or views.
n
Is a placeholder indicating that multiple Transact-SQL statements can be included in the trigger. For the IF UPDATE (column) statement, multiple columns can be included by repeating the UPDATE (column) clause.
IF UPDATE (column)
Tests for an INSERT or UPDATE action to a specified column and is not used with DELETE operations. More than one column can be specified. Because the table name is specified in the ON clause, do not include the table name before the column name in an IF UPDATE clause. To test for an INSERT or UPDATE action for more than one column, specify a separate UPDATE(column) clause following the first one. IF UPDATE will return the TRUE value in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.
Note The IF UPDATE (column) clause functions identically to an IF, IF...ELSE or WHILE statement and can use the BEGIN...END block. For more information, see Control-of-Flow Language.
UPDATE(column) can be used anywhere inside the body of the trigger.
column
Is the name of the column to test for either an INSERT or UPDATE action. This column can be of any data type supported by SQL Server. However, computed columns cannot be used in this context. For more information, see Data Types.
IF (COLUMNS_UPDATED())
Tests, in an INSERT or UPDATE trigger only, whether the mentioned column or columns were inserted or updated. COLUMNS_UPDATED returns a varbinary bit pattern that indicates which columns in the table were inserted or updated.
The COLUMNS_UPDATED function returns the bits in order from left to right, with the least significant bit being the leftmost. The leftmost bit represents the first column in the table; the next bit to the right represents the second column, and so on. COLUMNS_UPDATED returns multiple bytes if the table on which the trigger is created contains more than 8 columns, with the least significant byte being the leftmost. COLUMNS_UPDATED will return the TRUE value for all columns in INSERT actions because the columns have either explicit values or implicit (NULL) values inserted.
COLUMNS_UPDATED can be used anywhere inside the body of the trigger.
bitwise_operator
Is the bitwise operator to use in the comparison.
updated_bitmask
Is the integer bitmask of those columns actually updated or inserted. For example, table t1 contains columns C1, C2, C3, C4, and C5. To check whether columns C2, C3, and C4 are all updated (with table t1 having an UPDATE trigger), specify a value of 14. To check whether only column C2 is updated, specify a value of 2.
comparison_operator
Is the comparison operator. Use the equal sign (=) to check whether all columns specified in updated_bitmask are actually updated. Use the greater than symbol (>
to check whether any or some of the columns specified in updated_bitmask are updated.column_bitmask
Is the integer bitmask of those columns to check whether they are updated or inserted.
N 56°04'39.16"
E 12°55'05.25" - deleted and inserted are logical (conceptual) tables. They are structurally similar to the table on which the trigger is defined, that is, the table on which the user action is attempted, and hold the old values or new values of the rows that may be changed by the user action. For example, to retrieve all values in the deleted table, use:
-
Check the database setting for recursive triggers... it's usually "death by SQL" to have this setting enabled. Use the following to determine if it's on...
sp_dboption 'dbname','recursive triggers'
Obviously, you need to change 'dbname' to the name of the data base the table with the triggers is in.
If the setting is on, see your DBA to change the setting (if he/she will allow it).
Triggers do not normally call themselves unless this setting is on. Again, the default for this setting is OFF and now you see why.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
The triggers are triggering each other, anyway. you don't need two triggers:
- CREATE TRIGGER trigger_both ON MyTable AFTER INSERT, UPDATE
AS
- UPDATE MyTable
SET
- LastUpdateDate = GETDATE(),
- LastUpdateUser = SUSER_SNAME(),
Col05 = CASE WHEN MyTable.Col04 LIKE '155%' THEN 'Y' ELSE 'N' END
FROM
inserted
- JOIN
MyTable ON MyTable.DOCID = inserted.DOCID
In fact, Col05 can and should be a calc column, so you don't need to use the above trigger but can instead just use trigger1.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant - CREATE TRIGGER trigger_both ON MyTable AFTER INSERT, UPDATE
-
Yes... and the reason why they are triggering each other is because of the setting I mentioned... normally, triggers do not trigger each other.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
No... it's because the 'nested triggers' server option is on, which it is by default, i.e. normally triggers do trigger each other.
Setting 'recursive triggers' off at db level doesn't prevent indirect ('ping-pong') recursion, which is what will happen here with 2 triggers.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant -
Thank you for your replies. I had a feeling this was elementary. Thanks for taking it easy on me. LOL
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply