CODEWITHSUNDEEP
sqlsql-servertriggers
mdarling
mdarling

Reputation: 77

SQL Server trigger on update with select then insert into multiple tables

I am trying to create a trigger on update of a column and insert rows into other tables. The data is coming from a SELECT with multiple joins and needs to be inserted into multiple tables.

My question is, is it best practice to insert the values into variables before the insert? I have never created a trigger like this before.

CREATE TRIGGER ship_trigger ON dbo.Orders
FOR UPDATE
AS
SET NOCOUNT ON
IF ( UPDATE(OrderStatusId) AND OrderStatusId == 1)
BEGIN

SELECT
FROM
JOIN
JOIN
WHERE

I am just looking to understand the most efficient way to insert the data into the other tables

Thanks!

Upvotes: 1

Views: 1537

Answers (1)

Lamak
Lamak

Reputation: 70638

Assuming SQL Server (the syntax suggests so), it isn't a best practice to insert the values into variables first, what would be the point of doing that?. And the only way that you could actually do that would be on table variables or if you update only one row, since the triggers gets executed once for all the rows that got affected by your UPDATE, and those rows are available to you on the INSERTED pseudo table. So, your trigger would look something like this:

CREATE TRIGGER ship_trigger ON dbo.Orders
FOR UPDATE
AS
SET NOCOUNT ON

IF ( UPDATE(OrderStatusId) AND OrderStatusId = 1)
BEGIN

    INSERT INTO SomeTable(Col1, Col2)
    SELECT Col1, Col2
    FROM INSERTED

END

Upvotes: 4

Related Questions