DECLANSATORI

Un declansator (trigger) este o procedura care este executata in mod implicit, atunci cand asupra tabelului asociat este lansata o comanda INSERT, UPDATE sau DELETE.

Declansatorii pot contine instructiuni SQL sau Transact-SQL care sunt executate ca un intreg si pot invoca alte proceduri si alti declansatori. Decansatorul este invocat fie inaite, fie dupa sau poate inlocui executarea comenzii INSERT, UPDATE sau DELETE.

Limbajul Transact-SQL pastreaza doua tabele logice (conceptuale): unul numit deleted si altul numit inserted, Aceste tabele arata identic din punct de vedere al structurii cu tabelul in care este creat declansatorul. Ele sunt folosite pentru a pastra ultimele modificari facute tabelului curent.

La folosirea obiectelor declansator se presupune respectarea conditiilor:

Declansatoarele nu pot fi create in tabele temporare

Declansatoarele trebuie sa fie create in tabele din baza de date curenta

La eliminarea unui tabel, toate obicetele declansator asociate cu acest tabel sunt eliminate automat impreuna cu tabelul

Crearea unui declansator se realizeaza cu instructiunea:

CREATE TRIGGER trigger_name
ON
[ WITH ENCRYPTION ]

[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS
[ UPDATE ( column ) ]
[ n ]
| IF ( COLUMNS_UPDATED ( ) updated_bitmask )
column_bitmask [ n ]
} ]
instructiuni sql [ n ]
}
}

trigger_name

Este numele declansatorului (trigger-ului). Un nume de declansator trebuie sa respecte regulile pentru identificatori si trebuie sa fie unic in cadrul unei baze de date.

Table | view

Este tabele sau vederea in care declansatorul este executat.

WITH ENCRYPTION

Cripteaza intrarile in tabela syscomments care contine textul comenzii CREATE TRIGGER.

AFTER

Specifica faptul ca declansatorul este executat doar cand toate operatiile din enuntul SQL al declansatorului au fost execuate cu succes. Toate actiunile referentiale in cascada si verificarile de constrangere deasemenea trebuie sa reuseasca inaite de executarea acestui trigger.

Folosirea clauzei AFTER este identica cu folosirea clauzei FOR utilizata mai mult in ultimele versiuni de SQL Server.

Declansatorii AFTER nu pot fi definiti pe vederi.

INSTEAD OF

Specifica faptul ca declansatorul este executat in locul enuntului SQL declansat, astfel suprapunand operatiile din enuntul declansatorului.

Acest tip de declansator poate fi definit pe o tabela sau pe o vedere pentru INSERT, UPDATE sau DELETE.

Este posibil sa definiti vederi din vederi unde fiecare vedere sa aiba propriul declansator INSTEAD OF.

Sunt cuvinte cheie care specifica pentru ce operatie se executa declansatorul. Este obligatorie aparitia cel putin unei optiuni.

Pentru declansatori INSTEAD OF, optiunea DELETE nu este permisa pe tabele care au legaturi referentiale ce specifica o actiune in cascada pentru optiunea ON DELETE. Asemanator, optiunea UPDATE nu este permisa pe tabele care au legaturi referentiale ce specifica o actiune in cascada pentru optiunea ON UPDATE.

instructiuni sql[ n ]
Reprezinta conditia (conditiile) si actiunea (actiunile) declansatorului. Actiunile declansatorului sunt scrise in Transact-SQL si pot cuprinde oricate instructini sau comenzi Transact-SQL.

Cateva tabele speciale, de care am mai amintit, sunt folosite in instructiuea CREATE TRIGGER si anume: inserted si deleted.

IF UPDATE (column)

Testeaza actiunea unei comenzi INSERT sau UPDATE pentru coloana specificata si nu este folosit pentru DELETE. Mai multe coloane pot fi specificate. Deoarece numele tabelei este specificat dupa clauza ON, nu este nevoie sa includeti numele tabelei inainte de numele coloanei in clauza IF UPDATE. Pentru a testa o adaugare sau o modificare pentru mai multe coloane, specificati separat clauza UPDATE(column) dupa ce ati scris-o pe prima. IF UPDATE va returna valoarea TRUE in actiunea INSERT deoarece coloanele vor fi explicit sau implicit (NULL) inserate.

Nota Clauza IF UPDATE (column) functioneaza identic cu IF, IFELSE sau WHILE poate folosi si blocul BEGINEND.

UPDATE(column) poate fi folosit oriunde in corpul triggerului.

column

Este numele coloanei o pentru testarea actiunii INSERT sau UPDATE. Aceasta coloana poate avea orice tip de date SQL Server.Oricum coloanele calculate nu pot fi utilizate in acest context.

IF (COLUMNS_UPDATED())

Testeaza, doar intr-un declansator INSERT sau UPDATE, daca coloana sau coloanele mentionate au fost inserate sau modificate.

COLUMNS_UPDATED returneaza un tip varbinary (tip binar variabil) ce indica care coloane din tabel au fost inserate sau modificate.

Functia COLUMNS_UPDATED returneaza bitii in ordine de la stanga la dreapta, cu cel mai putin semnificativ bit in partea cea mai din stanga. Cel mai din stanga bit reprezentand prima coloana din tabel, urmatorul bit reprezentand cea de-a doua coloana etc.

COLUMNS_UPDATED returneaza mai multi octeti pentru declasatorul care a fost creat continant mai mult de 8 coloane, cu cel mai putin semnificativ byte in partea din stanga.

COLUMNS_UPDATED va returna TRUE pentru toate coloanele din actiunea INSERT deoarece coloanele au fost inserate cu valori explicite sau cu valori implicite (NULL).

COLUMNS_UPDATED poate fi folosit oriunde in corpul triggerului.

bitwise_operator

Operator pe bit folosit in comparatie.

updated_bitmask

Este o masca de tip intreg pentru acele coloanele care tocmai au fost modificate sau inserate. De exemplu, tabela t1 contine coloanele C1, C2, C3, C4, si C5. Pentru a verifica daca coloanele C2, C3, si C4 sunt toate modificate (tabela t1 avand un declansator UPDATE), specificati valoarea 14 (01110). Pentru a verifica daca doar coloana C2 este modificata, specificati valoarea 2(00010).

comparison_operator

Este operatorul de comparare. Folositi semnul egal (=) pentru a verifica daca toate coloanele specificate in updated_bitmask sunt realmente modificate.Folositi semnul mai mare (>) pentru a verifica daca oricare sau cateva din coloanele specificate in updated_bitmask sunt modificate.

column_bitmask

Este o masca de tip intreg pentru acele coloane pentru care verificam daca ele au fost modificate sau inserate.

SQL Server permite declansatori multiplii ce pot fi creati pentru fiecare eveniment de modificare de date (DELETE, INSERT, or UPDATE). De exemplu, daca este executat CREATE TRIGGER FOR UPDATE pentru o tabela care deja are un declansator UPDATE, atunci este creat un declansator aditional de tip UPDATE.

--acest declansator afiseaza pe ecran un mesaj pentru oricine incearca sa adauge sau sa modifice date din tabela Note

IF EXISTS (SELECT name FROM sysobjects WHERE name = 'trig_mesaj' AND type = 'TR')

DROP TRIGGER trig_mesaj

GO

CREATE TRIGGER trig_mesaj

ON Note

FOR INSERT, UPDATE

AS RAISERROR ('S-a executat un INSERT sau un UPDATE', 16, 1)

GO

INSERT INTO Note VALUES ('111','5',7,'11-10-2006')

Go

UPDATE Note SET Nota=5 WHERE NrLeg='111' and Cod_materie='5'

--acest declansator afiseaza pe ecran anumite mesaje pentru oricine incearca sa adauge sau sa modifice date din tabela Note

IF EXISTS (SELECT name

FROM sysobjects

WHERE name = 'trig1' AND type = 'TR')

DROP TRIGGER trig1

GO

CREATE TRIGGER trig1

ON Note

FOR INSERT, UPDATE

AS

DECLARE @@nota integer,

@@Nr_leg varchar(5)

SELECT @@Nr_leg = i.NrLeg, @@nota = i.Nota

FROM Note N, inserted i WHERE N.NrLeg = i.NrLeg

IF (@@nota <5 )and(@@Nr_leg like '111')

BEGIN

RAISERROR ('Studentul cu NrLeg=111 are note <5', 16, 1)

END

ELSE

BEGIN

RAISERROR ('Studentul cu NrLeg=%s are note de %d ',

16, 1, @@Nr_leg, @@nota)

END

go

INSERT INTO Note VALUES ('111','4',6,'11-10-2006')

go

INSERT INTO Note VALUES ('111','4',2,'12-01-2006')

Go

UPDATE Note SET Nota=5 WHERE NrLeg='111' and Cod_materie='4'

go

--declansator identic cu anteriorul cu conditia ca datele dorite a fi introduse sau modificate au fost sterse si astfel nu s-au facut modificari in tabela

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'trig1' AND type = 'TR')

DROP TRIGGER trig1

GO

CREATE TRIGGER trig1

ON Note

FOR INSERT, UPDATE

AS

DECLARE @@nota integer,@@Nr_leg varchar(5)

SELECT @@Nr_leg = i.NrLeg,

@@nota = i.Nota

FROM Note N, inserted i WHERE N.NrLeg = i.NrLeg

IF (@@nota <5 )and(@@Nr_leg like '111')

BEGIN

RAISERROR ('Studentul cu NrLeg=111 are note <6', 16, 1)

ROLLBACK TRANSACTION

END

ELSE

BEGIN

RAISERROR ('Studentul cu NrLeg=%s are note de %d ',

16, 1, @@Nr_leg, @@nota)

ROLLBACK TRANSACTION

END

go

INSERT INTO Note VALUES ('111','4',6,'11-10-2006')

Go

INSERT INTO Note VALUES ('111','4',2,'12-01-2006')

go

UPDATE Note SET Nota=5 WHERE NrLeg='111' and Cod_materie='4'

go

--acest declansator nu permite introducerea unor note nevalide

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'trig2' AND type = 'TR')

DROP TRIGGER trig2

GO

CREATE TRIGGER trig2

on Note

FOR insert, update

as

declare @nota integer

select @nota=Note.nota from Note, inserted WHERE Note.NrLeg=inserted.NrLeg

if(@nota<=0) or (@nota>10)

begin

RAISERROR ('Nota invalida', 16, 1)

ROLLBACK TRANSACTION

end

go

INSERT INTO Note VALUES ('115','4',-2,'12-01-2006')

go

SELECT * FROM Note WHERE NrLeg='115'

--acest declansator adauga 2 puncte la orice nota introdusa in tabela Note

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'trig3' AND type = 'TR')

DROP TRIGGER trig3

GO

CREATE TRIGGER trig3

ON Note

AFTER INSERT

AS

UPDATE Note

SET Note.nota = Note.nota + 2

FROM inserted

WHERE Note.NrLeg = inserted.NrLeg

go

INSERT INTO Note VALUES ('105','4',4,'12-01-2006')

go

SELECT * FROM Note WHERE NrLeg='105'

--acest declansator inlocuieste o operatie insert cu operatia UPDATE din enuntul SQL al declansatorului astfel daca se doreste inserarea unei note pentru un student la o anumita materie se va face o modificarea adica se adauga 1 punct la nota introdusa anterior la acea materie pentru studentul dat in tabela Note

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'trig3' AND type = 'TR')

DROP TRIGGER trig3

GO

CREATE TRIGGER trig3

ON Note

INSTEAD OF INSERT

AS

UPDATE Note

SET Note.nota = Note.nota+1

FROM inserted

WHERE Note.NrLeg = inserted.NrLeg

go

INSERT INTO Note VALUES ('105','4',4,'12-01-2006')

go

SELECT * FROM Note WHERE NrLeg='105'

--crearea unui declansator care afiseaza un mesaj daca s-a modificat campul Nota si un alt mesaj in caz contrar

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'trig4' AND type = 'TR')

DROP TRIGGER trig4

GO

CREATE TRIGGER trig4

ON Note

FOR UPDATE

AS

IF UPDATE(Nota)

RAISERROR ('S-a modificat campul Nota',16,1)

ELSE

RAISERROR ('S-a modificat alt camp al tabelei NOTE',16,1)

go

UPDATE Note SET Nota=10 WHERE NrLeg='115'

go

SELECT * FROM Note WHERE NrLeg='115'

go

UPDATE Note SET Cod_materie='3' WHERE NrLeg='115'

go

UPDATE Note SET Cod_materie='3', Nota=8 WHERE NrLeg='115'

--crearea unui declansator care afiseaza un mesaj daca s-au modificat campurile Nota si Cod_materie si '*****' in caz contrar

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'trig5' AND type = 'TR')

DROP TRIGGER trig5

GO

CREATE TRIGGER trig5

ON Note

FOR UPDATE

AS

IF UPDATE(Nota) AND UPDATE(Cod_materie)

RAISERROR ('S-au modificat campurile Nota si Cod_materie',16,1)

ELSE

RAISERROR ('*****',16,1)

go

UPDATE Note SET Nota=10 WHERE NrLeg='115'

go

SELECT * FROM Note WHERE NrLeg='115'

go

UPDATE Note SET Cod_materie='3', Nota=8 WHERE NrLeg='115'

--crearea unui declansator care afiseaza un mesaj daca s-au modificat campurile Nota sau Cod_materie si '*.*.*' in caz contrar

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'trig6' AND type = 'TR')

DROP TRIGGER trig6

GO

CREATE TRIGGER trig6

ON Note

FOR UPDATE

AS

IF (COLUMNS_UPDATED()&6)>0

RAISERROR ('S-au modificat campurile Nota sau Cod_materie',16,1)

ELSE

RAISERROR ('*.*.*',16,1)

go

UPDATE Note SET Nota=10 WHERE NrLeg='115'

go

SELECT * FROM Note WHERE NrLeg='115'

go

UPDATE Note SET Cod_materie='3', Nota=8 WHERE NrLeg='115'

go

UPDATE Note SET Data='3-3-2002' WHERE NrLeg='115'

--crearea unui declansator care verifica daca au fost modificate printr-o comanda UPDATE coloanele 3, 6 si 9 din tabele Student

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'trig7' AND type = 'TR')

DROP TRIGGER trig7

GO

CREATE TRIGGER trig7

ON Student

FOR UPDATE AS

IF ( (SUBSTRING(COLUMNS_UPDATED(),1,1)=power(2,(3-1))--coloana 3

+ power(2,(6-1))) --coloana 6

AND (SUBSTRING(COLUMNS_UPDATED(),2,1)=power(2,(1-1)))--coloana 9 (coloana 1 din cel de-al II-lea octet

)

PRINT 'Coloanele 3, 6 si 9 au fost modificate'

GO

UPDATE Student

SET Initiala='O', Data_nastere='12-12-1980', NrLeg_sot='115'

WHERE NrLeg='105'

GO

SELECT * FROM Student WHERE NrLeg='105'

--crearea unui declansator pentru DELETE

IF EXISTS (SELECT name FROM sysobjects

WHERE name = 'sterge_stud' AND type = 'TR')

DROP TRIGGER sterge_stud

GO

CREATE TRIGGER sterge_stud

ON Student

FOR DELETE

AS

DECLARE @NL varchar(5)

SELECT @NL=deleted.NrLeg FROM deleted

IF (cast(@NL as integer)>0)

PRINT 'S-a Sters'

ELSE

BEGIN

PRINT 'Acest NrLeg nu exista'

ROLLBACK TRANSACTION

END

GO

Delete From Student Where NrLeg='17'

Modificarea unui declansator se face cu comanda ALTER TRIGGER cu sintaxa:

ALTER TRIGGER trigger_name
ON ( table | view )
[ WITH ENCRYPTION ]

[ NOT FOR REPLICATION ]
AS
sql_statement [ n ]
}
|

[ NOT FOR REPLICATION ]
AS
UPDATE ( column ) ]
[ n ]
| IF ( COLUMNS_UPDATED ( ) updated_bitmask )
column_bitmask [ n ]
}
sql_statement [ n ]
}
}

Stergerea unui declasator se face cu comanda:

DROP TRIGGER [ ,n ]