August 12, 2004 at 8:42 am
I am trying to update a field (varchar) in a newly written record if that field is null. The update will be from another field (int) in the same newly written record. I am using a trigger but cannot manage to get it to do anything:
CREATE TRIGGER trNumber
ON tbl
FOR INSERT, UPDATE
AS
SELECT Number
FROM tbl
IF Number is null
BEGIN
set Number = ID
END
Any help would be appreciated.
Jeff
August 12, 2004 at 3:54 pm
You can try this:
CREATE TRIGGER trNumber
ON tbl
FOR INSERT, UPDATE
AS
IF (SELECT Number FROM inserted) is null
Update tbl set Number = (SELECT [ID] FROM inserted)
Anders Dæmroen
epsilon.no
August 13, 2004 at 7:35 am
Watch out for multi-row operations in triggers.
CREATE TRIGGER trNumber
ON tbl
FOR INSERT, UPDATE
AS
UPDATE t SET Number = i.ID
FROM tbl t
INNER JOIN inserted i ON t.pk = i.pk
WHERE i.Number IS NULL
August 13, 2004 at 9:08 am
Thanks alot guys. Everything worked great. Scott thanks for the addition I ran into the multi-row probkem at first. Once again this Forum has made my life easier and saved me a few hours of development time.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply