0

I need very simple task - replace date and time in two columns which is to be inserted into table. This spread out to very difficult thing. I tried lot of things, beginning with How can I edit values of an INSERT in a trigger on SQL Server?. Then I read about OUTPUT and tried this: 

CREATE TRIGGER RouteSheetRecordDatesCorrection ON dbo.RouteSheetRecords
AFTER INSERT 
AS  
BEGIN 
DECLARE @T TABLE
(
  [Id] int,
  [AccomplishmentDate] datetime2,
  [RouteSheetRecordOperationId] int,
  [WorkerName] varchar(50),
  [RouteSheetProductId] varchar(72),
  [Notes] varchar(500),
  [Location] varchar(100),
  [CreationDate] datetime2,
  [Creator] varchar(30),
  [Components] varchar(max)
)
UPDATE dbo.RouteSheetRecords
SET AccomplishmentDate = GETDATE(), CreationDate = GETDATE()
OUTPUT inserted.Id,
       inserted.AccomplishmentDate,
       inserted.RouteSheetRecordOperationId,
       inserted.WorkerName,
       inserted.RouteSheetProductId,
       inserted.Notes,
       inserted.Location,
       inserted.CreationDate,
       inserted.Creator,
       inserted.Components
INTO @T
WHERE Id = (SELECT Id FROM inserted)
END  
GO

But no matter what I try, I always get SqlException with this message when inserting new row:

The target table 'dbo.RouteSheetRecords' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.

Ondřej
  • 1,645
  • 1
  • 18
  • 29
  • Why are you using `OUTPUT` at all? If all you want to do is update those two columns, do -- there is no need to futz around with a second table. (Also, since they are `DATETIME2`, use `SYSDATETIME()`, not `GETDATE()` -- you're throwing away precision.) (Also, never forget `SET NOCOUNT ON` in your trigger.) (ALSO, use `WHERE Id IN`, not `WHERE Id = `. An update can affect as many rows as it damn well pleases.) – Jeroen Mostert Jun 28 '17 at 07:31
  • Note that if *other code* attempts to perform an insert/update on this table with an `OUTPUT` clause that does not use `INTO`, it will fail because of your trigger (with the message you've posted). The only recourse there is to rewrite that code, or remove your trigger. It isn't clear from the context if that's what's happening, though. – Jeroen Mostert Jun 28 '17 at 07:40
  • I am using OUTPUT because it was recommended at https://stackoverflow.com/questions/13198476/cannot-use-update-with-output-clause-when-a-trigger-is-on-the-table as solution to that exception. – Ondřej Jun 28 '17 at 07:54
  • Even something as simple as this throws abovementioned exception: CREATE TRIGGER RouteSheetRecordDatesCorrection ON dbo.RouteSheetRecords AFTER INSERT AS BEGIN UPDATE dbo.RouteSheetRecords SET AccomplishmentDate = SYSDATETIME(), CreationDate = SYSDATETIME() WHERE Id = (SELECT Id FROM inserted) END GO – Ondřej Jun 28 '17 at 08:09
  • Then show the statement that actually fails. It's not the trigger itself, but some external insert/update. Also verify if there are any other triggers on the table already. – Jeroen Mostert Jun 28 '17 at 09:02
  • No other triggers and error is displayed when I try to add new row in edit mode of SQL Server Management Studio. Same error when I send INSERT command from client application. – Ondřej Jun 28 '17 at 09:27
  • I can't reproduce your problem with the trigger as given in the comments and a simple `INSERT RouteSheetRecords DEFAULT VALUES`. The `INSERT` goes through and the columns are updated. You'll have to supply full code (including a `CREATE TABLE`) that reproduces this on an empty database. – Jeroen Mostert Jun 28 '17 at 09:32
  • Can you post exact SQL code for creating such working trigger? Unfortunately I can not post code for creating table because it is dependent on other tables so it will end up scripting half of database. – Ondřej Jun 28 '17 at 10:49

1 Answers1

0

Problem was that PetaPoco ORM was inserting OUTPUT statement when using Database.Insert method and this statement is not compatible with triggers. Solution is to not transer datetime2 columns into database and use default value in database or create stored procedure to insert data.

Ondřej
  • 1,645
  • 1
  • 18
  • 29