Download - SQL 07 Trigger
-
7/31/2019 SQL 07 Trigger
1/28
TRIGGERTRIGGER
-
7/31/2019 SQL 07 Trigger
2/28
2
Ni dung chi tit
Gii thiu To Trigger
Hiu chnh Trigger
Xa Trigger V d
-
7/31/2019 SQL 07 Trigger
3/28
3
Gii thiu
TRIGGER l mt Stored procedure c bit tng c chy mi khi c mt hnh ngno xy ra c lin quan n n.
Cc hot ng xy ra gip TRIGGER hotng: Insert, Delete, Update (DML).TRIGGER khng c thc thi mt cchtng minh nn cn thn trng khi dng
TRIGGER.
-
7/31/2019 SQL 07 Trigger
4/28
4
Gii thiu
TRIGGER hot ng gn ging cc hm bts kin trong javascript. N ch c kchhot khi xy ra mt hnh ng m c th l
cc thao tc Insert, Delete, Update. N hot ng mt cc th ng nn chng ta
khng th bit trigger thc thi khi no.
-
7/31/2019 SQL 07 Trigger
5/28
5
S dng trigger lm g?
m bo tnh rng buc ton vn cho CSDL. Kim sot d liu hin c trong CSDL khi c
thay i gi tr ca mt mu tin trong bng.
Kim tra d liu mi nhp vo c tha mn iukin khng.
Kim chng khi xa mu tin trong bng.
T ng cp nht d liu cho bng B khi d liu
bng A thay i (khi 2 bng c quan h vinhau).
-
7/31/2019 SQL 07 Trigger
6/28
6
Triggers hot ng nh th no?
Triggers c thc hin t ng sau khi lnhINSERT, UPDATE, hoc DELETE c thc hintrn mt table m trigger c nh ngha. Cncc constraints v INSTEAD OF trigger s c
kim tra trc khi lnh INSERT, UPDATE, hocDELETE thc hin.
Constraints s c kim tra trc trigger.
Mt table c th c nhiu Triggers cho mt action.
Mt trigger c th c nh ngha cho nhiu action. Th t thi hnh s l: trigger INSTEAD OF, cc
constraint, v sau cng l trigger AFTER.
-
7/31/2019 SQL 07 Trigger
7/28
7
Triggers hot ng nh th no?
Khi c nhiu trigger trong mt table, th table ownerc th dng procedure h thng sp_settriggerorder ch nh trigger u v trigger cui thc thi.Th t ca cc trigger cn li khng th sp xp
c. User phi c quyn thc hin tt c cc lnh m
c nh ngha trong Triggers
Table Owners khng th to ra cc Triggers trn
Views hoc Temporary Tables nhng c th thamchiu n view v temporary.
-
7/31/2019 SQL 07 Trigger
8/28
8
Triggers hot ng nh th no
Triggers khng tr kt qu v. Triggers c th iu khin multi-row actions: mt
hnh ng INSERT, UPDATE, hoc DELETE gimt trigger c th nh hng ln nhiu dng dliu, Ta c th chn:- X l tt c cc dng cng vi nhau trong trng
hp cc dng nh hng phi tha iu kin catrigger.
- X l tng dng tha iu kin.
-
7/31/2019 SQL 07 Trigger
9/28
9
Insert TRIGGER
c thc hin mi khi mu tin mi c chnvo bng
Mt bng tm Inserted s c sinh ra chamu tin cn chn
-
7/31/2019 SQL 07 Trigger
10/28
10
Delete TRIGGER
c thc hin mi khi cc mu tin trong bngb xa.
Mt bng tm Deleted c sinh ra lu ccmu tin b xa.
-
7/31/2019 SQL 07 Trigger
11/28
11
Update TRIGGER
c thc hin khi cc bn cc mu tin cabng c cp nht
Hai bng Inserted v Deleted s c sinh ra. Bng Inserted s lu thng tin cc mu tin mi
c sa, bng Deleted s lu thng tin ccmu tin c.
-
7/31/2019 SQL 07 Trigger
12/28
12
Instead of TRIGGER
Trigger cho php cp nht d liu cc bngthng qua view c lin kt nhiu bng
Hoc by trc khi cc constraint c tcdng.
-
7/31/2019 SQL 07 Trigger
13/28
13
Mt s ch :
Mt bng c nhiu trigger
Mi mt trigger c tn duy nht
Trong trigger thng dng mnh IFEXISTS
S dng trigger trong ton vn d liu
S dng trigger trong rng buc tham chiu.
-
7/31/2019 SQL 07 Trigger
14/28
14
Ni dung chi tit
Gii thiu To Trigger
Hiu chnh Trigger
Xa Trigger
V d
-
7/31/2019 SQL 07 Trigger
15/28
15
To Trigger
C php
- : tn Trigger
- : tn bng cn nh ngha Trigger
- INSERT, UPDATE, DELETE: nh ngha thao tc kchhot Trigger
CREATE TRIGGER ON
FOR< [INSERT] [,] [UPDATE] [,] [DELETE]>
AS
-
7/31/2019 SQL 07 Trigger
16/28
16
To Trigger (tt)
V d: rng buc lng nhn vin phi l s dng
CREATE TRIGGERLUONG_DUONG ON NHANVIEN
FOR INSERT, UPDATE
AS
BEGINIF (SELECT COUNT(*) FROM INSERTED WHERE LUONG < 0) > 0
BEGIN
Print N'Lng phi l s dng'
Rollback Tran
ENDEND
-
7/31/2019 SQL 07 Trigger
17/28
17
Ni dung chi tit
Gii thiu To Trigger
Hiu chnh Trigger
Xa Trigger
V d
-
7/31/2019 SQL 07 Trigger
18/28
18
Hiu chnh Trigger
C phpALTER TRIGGER ON
FOR
AS
-
7/31/2019 SQL 07 Trigger
19/28
19
Hiu chnh Trigger (tt)
V d: rng buc 0 0
BEGIN
Print0
-
7/31/2019 SQL 07 Trigger
20/28
20
Ni dung chi tit
Gii thiu To Trigger
Hiu chnh Trigger
Xa Trigger
V d
-
7/31/2019 SQL 07 Trigger
21/28
21
Xa Trigger
C php
V d
DROP TRIGGER
DROP TRIGGERLUONG_DUONG
-
7/31/2019 SQL 07 Trigger
22/28
22
Ni dung chi tit
Gii thiu To Trigger
Hiu chnh Trigger
Xa Trigger
V d
-
7/31/2019 SQL 07 Trigger
23/28
23
V d 1
Rng buc lng nhn vin phi tng
CREATE TRIGGERLUONG_TANG ON NHANVIEN
FOR UPDATE
AS
IF UPDATE(LUONG)BEGIN
IF (SELECT COUNT(*) FROM INSERTED I, DELETED D
WHERE D.LUONG >= I.LUONGAND I.MANV=D.MANV) > 0
BEGIN
Print N'Lng nhn vin phi tng'
Rollback Tran
END
END
-
7/31/2019 SQL 07 Trigger
24/28
24
V d 2
Rng buc nhn vin phi t 18 tui tr ln
CREATE TRIGGERTUOI_18_TROLEN ON NHANVIEN
FOR INSERT, UPDATE
AS
IF EXISTS (SELECT * FROM INSERTED I
WHERE DATEADD(YY,18,NGSINH) > GETDATE())
BEGIN
Print N'Nhn vin phi 18 tui tr ln'
Rollback Tran
END
-
7/31/2019 SQL 07 Trigger
25/28
25
V d 3
Rng buc khng cho php thm, sa bngNHANVIEN nu PHG khng c trong bngPHONGBAN
CREATE TRIGGERTONTAI_PHONG ON NHANVIEN
FOR INSERT, UPDATE
AS
IF NOT EXISTS (SELECT * FROM INSERTED , PHONGBAN
WHERE PHG = MAPHG)
BEGINPrint NM phng cha tn ti
Rollback Tran
END
-
7/31/2019 SQL 07 Trigger
26/28
26
V d 4
Rng buc khng cho sa MAPHG trong bngPHONGBAN
CREATE TRIGGERKHONG_SUA_KHOACHINH ON PHONGBAN
FOR UPDATE
ASIF UPDATE(MAPHG)
BEGIN
Print NKhng c sa kha chnh
Rollback Tran
END
-
7/31/2019 SQL 07 Trigger
27/28
27
V d 5
Sa MAPHG trong bng PHONGBAN th phi salun nhng mu tin c lin quan trong bngNHANVIEN
CREATE TRIGGERSUA_DAYCHUYEN ON PHONGBAN
FOR UPDATEAS
IF UPDATE(MAPHG)
BEGIN
UPDATE NHANVIEN
SET PHG = I.MAPHGFROM NHANVIEN, DELETED D, INSERTED I
WHERE PHG = D.MAPHG
END
-
7/31/2019 SQL 07 Trigger
28/28
28
V d 6
Khng cho xa NHANVIEN nu nhn vin ctrong bng PHANCONG
CREATE TRIGGERKHONG_XOA_NV_CO_PHANCONG ON NHANVIEN
FOR DELETE
AS
IF (SELECT COUNT(*) FROM DELETED , PHANCONG
WHERE MANV = MA_NVIEN) > 0
BEGIN
Print NNhn vin c trong phn cng
Rollback Tran
END