ABOUT TRIGGERS!!!
-
Hi everyone... I'm currently studying triggers and... well I have encountered some kind of problem I think...
Here is my very simple TRIGGER..
------------------------------------------------------------------
CREATE TRIGGER TRG_STOCKS ON [dbo].[T_STOCKS]
FOR INSERT, UPDATE
AS
BEGIN
DECLARE @ITEM_NO NUMERIC
DECLARE @DESC NVARCHAR(100)
SELECT @ITEM_NO = ITEM_NO FROM INSERTED
PRINT CAST(@ITEM_NO AS NVARCHAR)
IF UPDATE(ITEM_NO)
SET @DESC = 'NEW RECORD ' + CAST(@ITEM_NO AS NVARCHAR)
ELSE
SET @DESC = 'UPDATED RECORD ' + CAST(@ITEM_NO AS NVARCHAR)
UPDATE T_STOCKS SET DESCRIPTION = @DESC WHERE ITEM_NO = @ITEM_NO
END
----------------------------------------------------------------------
Now I have an insert statement like the one below...
----------------------------------------------------------------------insert into t_stocks
select
name,
description,
count
from
t_stocks
select * from t_stocks
----------------------------------------------------------------------
so there fore if I have 2 or more records already in t_stocks.. that insert statement would be like inserting 2 or more records as a batch...
The problem is that the trigger I created would only fire for the last record inserted... not for each record..
Can anyone explain to me why this is so? I'm quite new so I'm still having trouble figuring things out.
-
The trigger fires once for the entire statement. Using the INSERTED and DELETED virtual tables you can get access to all the rows that where inserted/deleted or updated (using both tables).
-
Dear All,
I was searching forum for any questions regarding triggers as I am new in using them. I coud not really understand how to use virtual table. Do you mean to say we need trigger to loop on inserted table and if so what is the syntax for this.
-
Taken from BOL
The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.
The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added simultaneously to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.
Sorry but I can't resist it.... (lets call it a moment of madness and leave it at that :hehe
The wonderful thing about triggers are triggers are wonderful things, their tops are made out of rubber, their bottoms are made out of springs. They're bouncy, trouncy, bouncy, trouncy, fun fun fun fun fun, but the most wonderful thing about triggers is i'm the only one.............
-
Well, that depends on what you want/need to do. You should try to use the virtual tables in a set-based fashion. Cursors and/or procedural code with loops will hurt performance.
-
Is this what you are trying to do?
CREATE TRIGGER TRG_STOCKS ON [dbo].[T_STOCKS]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE t
SET [DESCRIPTION] = (CASE WHEN d.ITEM_NO IS NULL THEN 'NEW RECORD ' ELSE 'UPDATE RECORD ' END)
+ CAST(t.ITEM_NO AS NVARCHAR)
FROM [T_STOCKS] t
INNER JOIN INSERTED i
ON i.ITEM_NO = t.ITEM_NO
LEFT OUTER JOIN DELETED d
ON d.ITEM_NO = t.ITEM_NO
END
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply