JChristen
JChristen

Reputation: 608

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.

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

Code:

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

The 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

UPDATE: Turns out that kCura's Relativity platform does not allow inserting with triggers, so this was futile...

Upvotes: 3

Views: 1515

Answers (1)

marc_s
marc_s

Reputation: 755053

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

Upvotes: 2

Related Questions