Menu
I have a super simple table that looks something like this:
CREATE TABLE [dbo].[TestTable](
[SomeColumn] [int] NOT NULL )
I also have a super simple trigger on another table that looks something like this:
ALTER TRIGGER [dbo].[trg_Audit_TableXYZ] ON [dbo].[TableXYZ] AFTER UPDATE
AS
INSERT INTO [dbo].[TestTable] Values (123)
My problem is that when the trigger runs I get the following error:
The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (2 rows).
I don't get it, why would I get this error?
Thank you.
903
The INSTEAD OF trigger is used to skip the DML statements such as ( DELETE, INSERT and UPDATE) and execute the SQL statements which are used inside the CREATE TRIGGER statement. Here’s the syntax of the FOR TRIGGER used in the CREATE TRIGGER statement by the following query:
As you may remember from previous chapters, triggers that handle update statements differ between those that only deal with insert statements, because they make use of deleted pseudo table as well. This is because an update statement can be considered as a delete followed by an insert.
A store procedure on a database table that is automatically launched or triggered after a SQL transaction and done successfully on the designated table is known as the AFTER UPDATE trigger in a SQL Server. When a time of status change needs to be recorded, it is useful. We can log, audit, or track adjustments using its assistance.
If you run this trigger as an AFTER UPDATE trigger, it runs recursively, since it always issues another UPDATE statement against the same table, which leads to another execution of the trigger. To work around this, you either need to make the update trigger an INSTEAD OF UPDATE trigger or test if the KENNSCHT column was modified at all.
Add SET NOCOUNT ON to the top of the trigger definition. This will suppress the additional rows affected message that emanates from the trigger and confuses SSMS.
i.e.
ALTER TRIGGER [dbo].[trg_Audit_TableXYZ]
ON [dbo].[TableXYZ]
AFTER UPDATE
AS
SET NOCOUNT ON
--Rest of trigger definition follows
INSERT INTO [dbo].[TestTable] Values (123)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
