Executing SQL from within an update trigger

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 ?
LVL 2
Roger AlcindorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roger AlcindorAuthor Commented:
Thanks Scott. Most helpful and much appreciated.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.