CODEWITHSUNDEEP
sqlsql-serversql-server-2008triggers
Luciano pinheiro
Luciano pinheiro

Reputation: 31

Can I update column value into a trigger when insert event happens?

I am using SQL Server 2008. Can I update a column value in the same table on which an insert trigger happens?

I.e., can I use this code?

CREATE TRIGGER tr_Table1_Insert_Table1name
ON Table1name
FOR INSERT AS

BEGIN

 UPDATE @NEW
    SET COLUMN = 3;

END
GO

If "No", how can make this happen?

Upvotes: 3

Views: 21536

Answers (3)

mehdi lotfi
mehdi lotfi

Reputation: 11571

You Must Use After Insert Trigger For Update Rows.

CREATE TRIGGER tr_Table1_Insert_Table1name ON Table1name 
    AFTER INSERT 
AS  BEGIN   
    UPDATE Table1
    SET COLUMN = 3
    Where ...  
END 
GO

Upvotes: 0

Tom H
Tom H

Reputation: 47464

If I understand your question correctly, this should do what you want:

CREATE TABLE dbo.Test_Insert_Trigger (
    my_id INT NOT NULL,
    my_string VARCHAR(20) NULL,
    CONSTRAINT PK_Test_Insert_Trigger PRIMARY KEY CLUSTERED (my_id)
)
GO
CREATE TRIGGER dbo.tri_Test_Insert_Trigger
ON Test_Insert_Trigger FOR INSERT
AS
BEGIN
    UPDATE T
    SET my_string = CAST(I.my_id AS VARCHAR(20))
    FROM
        INSERTED I
    INNER JOIN dbo.Test_Insert_Trigger T ON
        T.my_id = I.my_id
END
GO
INSERT INTO dbo.Test_Insert_Trigger (my_id) VALUES (1)
SELECT * FROM dbo.Test_Insert_Trigger

As OMG Ponies points out though, in many cases a DEFAULT constraint is what you'll really want here.

Upvotes: 2

Yuck
Yuck

Reputation: 50855

Yes, you can. There is no problem with your code since an UPDATE statement won't interfere with the INSERT you're overriding. You would want something like this:

UPDATE Table
SET Column = 3
WHERE Table.ID IN (SELECT ID FROM inserted)
  AND Table.ID NOT IN (SELECT ID FROM deleted);

This is assuming your table has an ID column.

Upvotes: 1

Related Questions