SQL Trigger

Un UPDATE pe o tabela poate afecta intre 0 si N rows. Indiferent cate row-ri sunt afectate trigger-ul se apeleaza o singura data pe UPDATE.
In trigger ai access la 2 pseudo-tabele numite “inserted” si “deleted”. Sunt 2 pseudo-tabele disponibile numai in trigger. Au aceeasi structura cu tabela pe care e def. triggerul si contine noile valori ale row-urilor updatate respectiv vechile valori.
Prin aceste 2 tabele se stie ce anume s-a updatat in statement. Ele sunt disponibile in toti trigerii doar ca in cei pe INSERT “deleted” e goala si pe DELETE “inserted” e goala.

exemplu cu “inserted”
CREATE TRIGGER [dbo].[UpdatePendingCalculationLockFlag]
ON [dbo].[PendingCalculation]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @transactionId uniqueidentifier
DECLARE @lockFlag int

DECLARE c1 CURSOR FOR
SELECT TransactionId, LockFlag FROM inserted
OPEN c1
WHILE 1 = 1
BEGIN
FETCH FROM c1 INTO @transactionId, @lockFlag
IF @@FETCH_STATUS != 0
BREAK

IF (@lockFlag = 2)
UPDATE LoadTest_PendingCalculation
SET TimeStampBLF2 = GETDATE()
WHERE TransactionId = @transactionId

IF (@lockFlag = 3)
UPDATE LoadTest_PendingCalculation
SET TimeStampBLF3 = GETDATE()
WHERE TransactionId = @transactionId
END
CLOSE c1
DEALLOCATE c1
END

Ce urmaresc in acest exemplu: la fiecare update care se intampla in tabela parinte (PendingCalculation) vreau sa modific ceva in tabela secundara (LoadTest_PendingCalculation). De aceea printr-un cursor parcurg eventualele randuri modificate (pseudo-tabela inserted).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s