Executing SQL from within an update trigger

Roger Alcindor
Roger Alcindor used Ask the Experts™
on
I am trying to write a trigger on a table in an SQL Server Database (SQL Server 14)
I wish to write a record to another table when the Prodschedule table is updated

My code is as shown below but gives an error:
 "Msg 156, Level 15, State 1, Procedure OnLoadOffLoad2, Line 31
Incorrect syntax near the keyword 'from'."

alter TRIGGER [dbo].[OnLoadOffLoad2] ON [dbo].[ProdSchedule]
  
   AFTER UPDATE
AS 
BEGIN
	declare @p char(12)

	set @p = ProdSchedule.HondaLotNum
	from ProdSchedule join updated
	on ProdSchedule.ProdScheduleID = updated.ProdScheduleID

	execute('insert ScheduleLog (Lot,updated) VALUES(p,getdate()) ')

END
GO

Open in new window


Is what I am trying to do possible ?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Senior DBA
Most Valuable Expert 2018
Top Expert 2014
Commented:
ALTER TRIGGER [dbo].[OnLoadOffLoad2] ON [dbo].[ProdSchedule]  
   AFTER UPDATE
AS
SET NOCOUNT ON;
    insert into ScheduleLog ( Lot, updated )
    select ProdSchedule.HondaLotNum, GETDATE()
      from ProdSchedule join inserted i
      on ProdSchedule.ProdScheduleID = i.ProdScheduleID
GO


Btw, the original error was because you needed to use "select" and not "set":
select @p = ProdSchedule.HondaLotNum

Author

Commented:
Thanks Scott. Most helpful and much appreciated.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial