August 9, 2002 at 7:24 am
I'm dabbling with Triggers in SQL Server 2000.
I've quite quickly got a few simple triggers up and running, but I have stumbled at my first more complicated attempt.
Basically what I want to do is check that when a column in my table is updated with a certain value, I want to insert a record into another table.
I know how to check to see if a column is updated, but not how to check if it is updated with a certain value.
This is what I've got so far:
---------------------------------------------
CREATE TRIGGER ClientUpdatesTrigger ON Table1
FOR UPDATE
AS
DECLARE @MyID nvarchar(50)
SET @MyID = 'Woohoo!'
IF EXISTS
(
SELECT 'True'
FROM INSERTED AS I
JOIN Table1 AS T ON T.ID=I.ID
WHERE T.Status='Taken'
)
INSERT INTO Table2 (ID) VALUES (@MyID)
-----------------------------------------
What happens here is that if the field called Status in Table1 gets updated with 'Taken' a record is inserted into Table2 containing the value of @MyID
My problem now is that for testing purposes I've called my variable @MyID and assigned it a value of 'Woohoo!', what I really want to do is assign @MyID the value of the field ID from Table1 for that particular record I've updated in Table1.
I just have no idea how to go about it.
Windows 2008 Server | SQL Server 2008
August 9, 2002 at 8:13 am
Triggers are set based, they fire once per transaction, not once per row, so anytime you declare variables to work with you should plan on using a cursor. In this case you shouldnt need a cursor, do something like this:
insert into tbl2(col1)
select col1 from inserted i inner join table2 t on i.pkey=t.pkey where t.status='Taken'
Something like that anyway!
Andy
August 9, 2002 at 8:16 am
Try this :
CREATE TRIGGER ClientUpdatesTrigger ON Table1
FOR UPDATE
AS
DECLARE @MyID nvarchar(50)
if exists(select id from inserted where status = 'Taken')
insert into testtrig2 (id)
(SELECT id FROM inserted where status = 'Taken')
end
Should do the trick.
August 9, 2002 at 8:25 am
Speedy replies - thanks guys, I'll try it out.
Windows 2008 Server | SQL Server 2008
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply