January 8, 2004 at 12:21 pm
Hi:
How can I change the value of one field in an INSERT trigger?
For ex:
Table1 have 2 fields (F1 and F2).
INSERT INTO Table1 (F1, F2) VALUES (3,'TEST');
In the trigger I want to change the value 3 for another value.
Thanks
January 8, 2004 at 1:28 pm
Take a look at INSTEAD OF triggers in BOL
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
January 9, 2004 at 12:13 pm
As Frank says, you should look up triggers in the BOL. The INSTEAD OF trigger is probably the most elegant way to handle this, but you can do this with either INSTEAD OF triggers or "normal" triggers.
Since a picture can be worth MAXINT words, here's a script that assigns one of *each* type of trigger to the table. The "normal" trigger adds 10 to the inserted value, and the INSTEAD OF trigger adds 100, so that if you insert a 3, you wind up with 113. Hope the code samplet is helpful.
Chris
CREATE TABLE T1 (F1 INT PRIMARY KEY, F2 VARCHAR(15))
GO
CREATE TRIGGER T1_INSERT_TRIG ON T1 FOR INSERT
AS
BEGIN
UPDATE T1 SET F1 = F1 + 10 WHERE T1.F1 IN (SELECT F1 FROM INSERTED)
END
GO
CREATE TRIGGER T1_INSERT_TRIG2 ON T1 INSTEAD OF INSERT
AS
BEGIN
INSERT INTO T1 SELECT F1 + 100, F2 FROM INSERTED
END
GO
INSERT INTO T1 (F1, F2) VALUES (3,'TEST')
SELECT * FROM T1
Output:
(1 row(s) affected)
(1 row(s) affected)
F1 F2
----------- ---------------
113 TEST
(1 row(s) affected)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply