0

The following trigger I have wrote to, upon insertion of an Operator name in a record, update the SetHours field with data from another table which contains that operators hours. Tis does not live update, which I would like. It updates when the table is next viewed.

ALTER TRIGGER [dbo].[FindOperatorHours]
ON [dbo].[tblTime]
INSTEAD OF INSERT
AS 
BEGIN  
  INSERT tblTime (SetHours)
    SELECT ISNULL(INSERTED.SetHours, tblUser.OperatorHours) AS SetHours
    FROM INSERTED
    JOIN tblUser ON INSERTED.Operator = tblUser.UserID
END

This coverts all data apart from SetHours to NULL upon re-open of the table, however the value has been fetched correctly? Is it possible to get this to live update, and not convert my data to null?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Ollie Sharratt
  • 95
  • 14
  • Possible duplicate of [SQL Server change column value on insert trigger](https://stackoverflow.com/questions/25326987/sql-server-change-column-value-on-insert-trigger) – JeffUK Feb 18 '19 at 23:59
  • you need to select all the other columns and include them in your insert statement in the trigger. The INSTEAD OF trigger doesnt insert anything apart from what you do. Or use an after trigger instead and then update the rows that this time have been inserted already – Martin Smith Feb 19 '19 at 00:02
  • Thanks Martin - I thought as much, I was mixing up my syntax and is wasn't working, I have now had some success – Ollie Sharratt Feb 19 '19 at 00:08

1 Answers1

1

This is inserting new rows, so you need to include all the columns:

ALTER TRIGGER [dbo].[FindOperatorHours]
ON [dbo].[tblTime]
INSTEAD OF INSERT
AS 
BEGIN  
  INSERT tblTime ( . . . , SetHours)
    SELECT . . ., COALESCE(i.SetHours, tblUser.OperatorHours) AS u
    FROM INSERTED i JOIN
         tblUser u
         ON i.Operator = u.UserID;
END;

Include all the other columns in the . . ..

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786