�ݺ�ߣ

DB2
-- Chu y, trong DB2, trong CSDL cua ban co the co schema khac NCKH, co the co
tablespace khac USERSPACE1
CREATE TABLE NCKH.LOPSH ( MALOPSH VARCHAR (10) NOT NULL ,
MAKHOA CHARACTER (6) NOT NULL , SISO SMALLINT WITH DEFAULT 0 ,
CONSTRAINT CC1331778626427 PRIMARY KEY ( MALOPSH) ) IN
USERSPACE1 ;
COMMENT ON TABLE NCKH.LOPSH IS 'Lop Sinh Hoat';
CREATE TABLE NCKH.SV ( MASV CHARACTER (11) NOT NULL , HOTENSV
VARGRAPHIC (50) NOT NULL , MALOPSH VARCHAR (10) NOT NULL ,
CONSTRAINT CC1331779384063 PRIMARY KEY ( MASV) ) IN USERSPACE1 ;
CREATE OR REPLACE TRIGGER NCKH.TRG_SV_AI AFTER INSERT ON
NCKH.SV REFERENCING NEW_TABLE AS n FOR EACH STATEMENT MODE
DB2SQL
-- ATOMIC = nguyen to de ca khoi BEGIN…END duoc thuc thi theo kieu hoac hoan
toan, hoac la khong
BEGIN ATOMIC
-- Kiem tra xem, co gia tri MaLopSH nao chua co trong bang LOPSH hay khong
DECLARE cnt INT;
SET cnt = (SELECT COUNT(*) FROM n LEFT OUTER JOIN LOPSH ON
n.MaLopSH = LOPSH.MaLopSH WHERE LOPSH.MaLopSH IS NULL);
IF (cnt > 0) THEN
-- Dua ra thong bao va ROLLBACK TRANSACTION
SIGNAL SQLSTATE '80000' ('Mot so ban ghi duoc them moi chua cac
gia tri MaLopSH khong co trong bang LOPSH!');
END IF;
-- Cho moi ban ghi trong ket qua cua SELECT
-- O day, ta lam theo cach toi uu hon, nghia la thay vi moi lan cho moi ban ghi
duoc them moi ta +1, ta tinh tong roi cong mot lan
FOR myRecord AS SELECT MaLopSH, COUNT(MaSV) AS cnt FROM n
GROUP BY MaLopSH DO
UPDATE LOPSH SET SiSo = SiSo + myRecord.cnt WHERE MaLopSH
= myRecord.maLopSH;
END FOR;
-- Chu y: Statement termination character duoc quy dinh la ^, chu y o phan duoi cua so
Command editor, nen ta dung END^
-- Neu la @, thi END@
END^

-- Dung cac truy van sau day de kiem tra:
-- INSERT INTO LOPSH (MaLopSH, maKhoa) VALUES('L1', 'Khoa12'),('L2',
'Khoa12');
-- SELECT * FROM LOPSH; -- SiSo cho L1 va L2 la gi??? 0, 0
-- INSERT INTO SV(MaSV, HoTenSV, MaLopSH) VALUES('12345678901',
N'Nguyeexn','L1'), ('12345678902', N'Nguyeexn','L1'), ('12345678903',
N'Nguyeexn','L2');
-- Neu them moi ban ghi co MaLopSH = ‘L3’ chua co trong bang LOPSH thi se nhan
duoc thong bao loi
-- INSERT INTO SV(MaSV, HoTenSV, MaLopSH) VALUES('12345678907',
N'Nguyeexn','L3')

SQL SERVER
Muon chay dong nao thi bo comment cua dong do. Comment: --, /*.....*/.
CHua co thoi gian. Se co the cho truong hop UPDATE, cung nhu trong DB2. Cac ban co gang gui
cho cac ban khac ko phai la thanh vien lop 35k14.
--CREATE TABLE LOPSH(MaLopSH VARCHAR(10) NOT NULL PRIMARY KEY, MaKhoa
CHAR(6) NOT NULL, SiSo INT DEFAULT 0);
--CREATE TABLE SV(MaSV CHAR(11) NOT NULL PRIMARY KEY, HoTenSV NVARCHAR(50)
NOT NULL, MaLopSH VARCHAR(10) NOT NULL);
/*
-- Neu dieu chinh chu ko phai tao moi, ta dung ALTER thay vi CREATE
-- Co the google: T-SQL CREATE TRIGGER
CREATE TRIGGER [dbo].[trg_SV_BI]
ON [dbo].[SV]
FOR INSERT
AS
BEGIN
SET NOCOUNT ON;
-- Insert statements for trigger here
-- Khai bao bien, chu y kieu du lieu va kich thuoc
DECLARE @maLop [varchar](10);
-- Khai bao bien cu-so, co the google: T-SQL CURSOR
DECLARE myCursor CURSOR FOR
SELECT maLopSH FROM inserted;
-- Mo cu-so
OPEN myCursor;
-- Lay ban ghi ke tiep va cho vao bien @maLop, vi tri ban dau khong phai o ban ghi dau
tien
FETCH NEXT FROM myCursor
INTO @maLop;
-- Khi con co the lay ban ghi ke tiep tu cu-so, neu # 0 - loi, nghia la khong the lay tu cu-so
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE LOPSH SET SiSo = SiSo + 1 WHERE maLopSH = @maLop;
FETCH NEXT FROM myCursor
INTO @maLop;
END;
-- Dong cu-so sau khi da su dung xong
CLOSE myCursor;
-- Giai phong bo nho da phan bo cho cu-so
DEALLOCATE myCursor;
END;
*/
-- insert cung luc nhieu ban ghi, SiSo ban dau tu dong = 0
-- INSERT INTO LOPSH (MaLopSH, maKhoa) VALUES('L1', 'Khoa12'),('L2', 'Khoa12');

-- INSERT INTO SV(MaSV, HoTenSV, MaLopSH) VALUES('12345678901', N'Nguyeexn','L1'),
('12345678902', N'Nguyeexn','L1'), ('12345678903', N'Nguyeexn','L2');
/*
CREATE TRIGGER [dbo].[trg_SV_AU]
ON [dbo].[SV]
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @maSinhVien [char](11);
DECLARE @HoTen [nvarchar](50);
-- Khai bao bien cu-so
DECLARE myCursorD CURSOR FOR
SELECT maLopSH FROM deleted;
-- Mo cu-so
OPEN myCursorD;
-- UPDATE mot ban ghi = DELETE mot ban ghi (chua cac gia tri cu cua
UPDATE) + INSERT mot ban ghi (chua cac gia tri moi cua UPDATE)
-- Lay ban ghi ke tiep va cho vao bien @maLop, vi tri ban dau khong phai
o ban ghi dau tien
FETCH NEXT FROM myCursorD
INTO @maLop;
-- Khi con co the lay ban ghi ke tiep tu cu-so, neu # 0 - loi, nghia la khong
the lay tu cu-so
BEGIN
UPDATE LOPSH SET SiSo = SiSo - 1 WHERE maLopSH =
@maLop;
INTO @maLop;
END;
CLOSE myCursorD;
DEALLOCATE myCursorD;
DECLARE myCursorI CURSOR FOR
SELECT maSV, HoTenSV, maLopSH FROM inserted; -- co the
select tat ca, co the mot vai cot

-- Mo cu-so
OPEN myCursorI;
-- Lay ban ghi ke tiep va cho vao bien @maLop, vi tri ban dau khong phai
o ban ghi dau tien
FETCH NEXT FROM myCursorI
INTO @maSinhVien, @HoTen, @maLop; -- khi khai bao cu-so co
bao nhieu cot thi gio select ra cungphai bay nhieu bien tuong ung bay
nhieu cot
-- Khi con co the lay ban ghi ke tiep tu cu-so, neu # 0 - loi, nghia la khong
the lay tu cu-so
BEGIN
UPDATE LOPSH SET SiSo = SiSo + 1 WHERE maLopSH =
@maLop;
FETCH NEXT FROM myCursorI
INTO @maSinhVien, @HoTen, @maLop;
END;
CLOSE myCursorI;
DEALLOCATE myCursorI;
END;
*/
-- UPDATE SV SET MaLopSH = 'L2' WHERE MaSV = '12345678901';
--SELECT * FROM LOPSH; -- SiSo cua L1, L2 la 1, 2
/*
CREATE TRIGGER [dbo].[trg_SV_AD]
ON [dbo].[SV]
AFTER DELETE
AS
BEGIN
SET NOCOUNT ON;
DECLARE myCursorD CURSOR FOR
SELECT maLopSH FROM deleted;
-- Mo cu-so
OPEN myCursorD;
INTO @maLop;
-- Khi con co the lay ban ghi ke tiep tu cu-so, neu # 0 - loi, nghia la
khong the lay tu cu-so
BEGIN
UPDATE LOPSH SET SiSo = SiSo - 1 WHERE maLopSH = @maLop;

INTO @maLop;
END;
CLOSE myCursorD;
DEALLOCATE myCursorD;
END;
-- DELETE FROM SV WHERE MaSV = '12345678901';
-- SELECT * FROM LOPSH; -- SiSo cua L1, L2 la 1, 1
*/

�ݺ�ߣ

Hqt csdl 2 trigger - db2-sql server - examples

Recommended

More Related Content

What's hot (18)

Viewers also liked (10)

Hqt csdl 2 trigger - db2-sql server - examples