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

Question

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?

Solution 1

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.

Solution 2

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.

Comments

[OMG Ponies]

I don't understand what you're wanting to do -- that, or why you wouldn't use a DEFAULT constraint on the column...

[Yuck]

@OMG Ponies: Yeah, +1 to that. If all the OP wants is to provide a default value he should be doing it in the table definition. If he's instead wanting to make sure that a user can't provide a different value for an inserted record a trigger may be the right way to go.

[Tom H]

Did you try it? It's a pretty simple change to the script above. I'll give you a hint though: yes.

[Luciano pinheiro]

My friend, Can I make this code? <code> UPDATE INSERTED SET ID = 3;

[Yuck]

@Luciano: You can't modify the inserted table in a trigger.

[Luciano pinheiro]

That IS what I should know! Thanks.

[Tom H]

You can't update the system INSERTED table, but you can update the table which caused the insert trigger to fire.

[Andriy M]

The OP uses has declared their trigger as FOR INSERT, which is the same, because AFTER INSERT = FOR INSERT in SQL Server. The issue has to do with the incorrect updating.

[Andriy M]

As a matter of fact, you don't need that second test (NOT IN deleted) in a FOR INSERT trigger. Although it doesn't change anything, of course.