CODEWITHSUNDEEP
sql-servertriggers
Node17
Node17

Reputation: 537

trigger not updating sql

Although this is completed successfully on completion, it is not having the desired update.

CREATE TRIGGER Trigger1
On dbo.[table1] 
FOR UPDATE
AS
Declare @Id int;
SELECT @Id = Issue_Id FROM dbo.[table1]

INSERT INTO dbo.[storage]
    SELECT Id, Title, project, Problem
    FROM dbo.[table2]
    WHERE Id = @Id

Is there something I am doing wrong or that I can't use variables within the scope of a trigger?

Many thanks

Upvotes: 1

Views: 769

Answers (5)

Stackoverflow
Stackoverflow

Reputation: 21

below line should be removed

SELECT @Id = Issue_Id FROM dbo.[table1]

It should be following.

SELECT Issue_Id FROM Inserted

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239824

The others have correctly answered that you should be using inserted and a join, to build a proper trigger. But:

Based on your comments to other's answers - you should never attempt to access any resource outside of your own database from a trigger, let along from another server.

Try to decouple the trigger activity from the cross server activity - say have your trigger add a row to a queue table (or use real service broker queues), and have an independent component be responsible for servicing these requests.

Otherwise, if there are any e.g. network issues, not only does your trigger break, but it forces a rollback for the original update also - it makes your local database unusable.

This also means that the independent component can cope with timeouts, and perform appropriate retries, etc.

Upvotes: 1

Pankaj
Pankaj

Reputation: 10115

Please go through the below suggestion.

Instead of the below line

SELECT @Id = Issue_Id FROM dbo.[table1]

It had to be following.

SELECT Issue_Id FROM Inserted

Following is the updated one.

CREATE TRIGGER Trigger1
On dbo.[table1] 
FOR UPDATE
AS
SET NOCOUNT ON
Declare @Id int;

With CTE as 
(
    SELECT Issue_Id FROM Inserted I
    Inner Join [table1] T on T.Issue_Id  = I.Issue_Id
)

INSERT INTO dbo.[storage]
SELECT Id, Title, project, Problem
FROM dbo.[table2]
Inner Join CTE c on c.Issue_Id = Id

For more information

In SQL server the records which are being inserted / modified or deleted occupies themselves in two temporary tables available in a DML trigger. These tables are INSERTED and DELETED. The INSERTED table has inserted or updated records. The DELETED table has the old state of the records being updated or deleted.

Upvotes: 2

gbn
gbn

Reputation: 432742

To support multirow updates

CREATE TRIGGER Trigger1 On dbo.[table1] FOR UPDATE
AS
SET NOCOUNT ON

INSERT INTO dbo.[storage]
    SELECT t.Id, t.Title, t.project, t.Problem
    FROM dbo.[table2] t
        JOIN INSERTED I ON t.ID = I.ID
GO

If table2 is actually table1 (which makes more sense: how is table1 related to storage and table2?)...

CREATE TRIGGER Trigger1 On dbo.[table1] FOR UPDATE
AS
SET NOCOUNT ON

INSERT INTO dbo.[storage]
    SELECT Id, Title, project, Problem
    FROM INSERTED
GO

Upvotes: 4

Jon Egerton
Jon Egerton

Reputation: 41589

To handle multple updates and the inserted table in one go:

CREATE TRIGGER Trigger1
On dbo.[table1] 
FOR UPDATE
AS

INSERT INTO dbo.[storage]
    SELECT Id, Title, project, Problem
    FROM dbo.[table2] t2
         JOIN Inserted i ON i.Issue_ID = t2.Id

Upvotes: 2

Related Questions