SQL Server: Insert row with trigger after Insert
I’m trying to insert another row into a table whenever a row is inserted using a trigger, but get the following error message:
The target table ‘EDDSDBO.Redaction’ of the DML statement cannot have
any enabled triggers if the statement contains an OUTPUT clause
without INTO clause.
Passing parameter to a SQL Server stored procedure SQL Server XML output with CDATA Grouping pairs in sql Create Assembly fails because methods are not visible - but everything is public JPA 2.1 call stored procedure in sql server Subtracting count of rows in subquery from current query
Any help creating a work-around for this would be greatly appreciated.
From reading the links below my code is currently the following
Cannot use UPDATE with OUTPUT clause when a trigger is on the table
SQL Server Helper workaround
ALTER TRIGGER [EDDSDBO].[AddLabel] ON [EDDSDBO].[Redaction] AFTER INSERT AS BEGIN DECLARE @T TABLE ( [FileGuid] VARCHAR, [X] INT, [Y] INT, [Width] INT, [Height] INT ) INSERT INTO [Redaction] [FileGuid],[X],[Y],[Width],[Height] OUTPUT [inserted].[FileGuid], [inserted].[X], [inserted].[Y], [inserted].[Width], [inserted].[Height] INTO @T SELECT [inserted].[FileGuid], [inserted].[X], [inserted].[Y], 70, 35 FROM inserted SELECT * FROM @T END
INSERT code was originally the following before reading the links described:
INSERT INTO [Redaction] [FileGuid],[X],[Y],[Width],[Height] SELECT TOP 1 [FileGuid], [X], [Y], 70, 35 FROM [Redaction] AS r1 ORDER BY [ID] DESC
Turns out that kCura’s Relativity platform does not allow inserting with triggers, so this was futile…
One Solution collect form web for “SQL Server: Insert row with trigger after Insert”
Since you’re inside a trigger, the trigger itself already defines the
Inserted pseudo table for its use.
You cannot mix this with the
Inserted alias that the
OUTPUT clause would need.
Therefore, you cannot use the
OUTPUT clause in a statement inside a trigger.
Also: triggers should do their work silently and not produce any result sets!
UPDATE: the proper syntax for
INSERT (which would be easily found on MSDN…) is:
INSERT INTO [Redaction] ([FileGuid], [X], [Y], [Width], [Height]) SELECT TOP 1 [FileGuid], [X], [Y], 70, 35 FROM [Redaction] AS r1 ORDER BY [ID] DESC
You need to add parenthesis around the list of columns in the
INSERT INTO line