/*****************/ /* 13Àå. Æ®¸®°Å */ /*****************/ /* ------------------------------- <½Ç½À1> p623 ~ p624 ---------------------------------*/ USE tempdb; CREATE TABLE testTbl (id INT, txt NVARCHAR(5)); GO INSERT INTO testTbl VALUES(1, '¿ìÀç³²'); INSERT INTO testTbl VALUES(2, 'Áö¿îÀÌ'); INSERT INTO testTbl VALUES(3, 'ÇÑÁÖ¿¬'); CREATE TRIGGER testTrg -- Æ®¸®°Å À̸§ ON testTbl -- Æ®¸®°Å¸¦ ºÎÂøÇÒ Å×À̺í AFTER DELETE, UPDATE -- »èÁ¦,¼öÁ¤ÈÄ¿¡ ÀÛµ¿Çϵµ·Ï ÁöÁ¤ AS PRINT('Æ®¸®°Å°¡ ÀÛµ¿Çß½À´Ï´Ù') ; -- Æ®¸®°Å ½ÇÇà½Ã ÀÛµ¿µÇ´Â ÄÚµåµé INSERT INTO testTbl VALUES(4, '´çÅÁÀÌ'); UPDATE testTbl SET txt = '¿ìÁö¿î' WHERE id = 2; DELETE testTbl WHERE id = 4; /* ------------------------------- <½Ç½À2> p626 ~ p628 ---------------------------------*/ USE master ; RESTORE DATABASE sqlDB FROM DISK ='c:\sqldb.bak' WITH REPLACE ; USE sqlDB; DROP TABLE buyTbl; -- ±¸¸ÅÅ×À̺íÀº ÇÊ¿ä¾øÀ¸¹Ç·Î »èÁ¦. CREATE TABLE backup_userTbl ( userID nchar(8) , name nvarchar(10) , birthYear int , addr nchar(4) , mobile1 nchar(3), mobile2 nchar(8), height smallint , modType NCHAR(2), -- º¯°æµÈ ŸÀÔ. '¼öÁ¤' ¶Ç´Â '»èÁ¦' modDate datetime, -- º¯°æµÈ ³¯Â¥ modUser nvarchar(256) -- º¯°æÇÑ »ç¿ëÀÚ ) CREATE TRIGGER trg_BackupUserTbl -- Æ®¸®°Å À̸§ ON userTbl -- Æ®¸®°Å¸¦ ºÎÂøÇÒ Å×À̺í AFTER UPDATE,DELETE -- »èÁ¦,¼öÁ¤ ÈÄ¿¡ ÀÛµ¿Çϵµ·Ï ÁöÁ¤ AS DECLARE @modType NCHAR(2) -- º¯°æ ŸÀÔ IF (COLUMNS_UPDATED() > 0) -- ¾÷µ¥ÀÌÆ® µÇ¾ú´Ù¸é BEGIN SET @modType = '¼öÁ¤' END ELSE -- »èÁ¦µÇ¾ú´Ù¸é, BEGIN SET @modType = '»èÁ¦' END -- delete Å×À̺íÀÇ ³»¿ë(º¯°æÀüÀÇ ³»¿ë)À» ¹é¾÷Å×ÀÌºí¿¡ »ðÀÔ INSERT INTO backup_userTbl SELECT userID, name, birthYear, addr, mobile1, mobile2, height, @modType, GETDATE(), USER_NAME() FROM deleted UPDATE userTbl SET addr = '¹Ì±¹' WHERE userID = 'JJJ'; DELETE userTbl WHERE height >= 180; SELECT * FROM backup_userTbl; TRUNCATE TABLE userTbl; SELECT * FROM backup_userTbl; CREATE TRIGGER trg_insertUserTbl ON userTbl AFTER INSERT -- »ðÀÔ ÈÄ¿¡ ÀÛµ¿Çϵµ·Ï ÁöÁ¤ AS RAISERROR(N'µ¥ÀÌÅÍÀÇ ÀÔ·ÂÀ» ½ÃµµÇß½À´Ï´Ù.',10,1) RAISERROR(N'±ÍÇÏÀÇ Á¤º¸°¡ ¼­¹ö¿¡ ±â·ÏµÇ¾ú½À´Ï´Ù.',10,1) RAISERROR(N'±×¸®°í, ÀÔ·ÂÇÑ µ¥ÀÌÅÍ´Â Àû¿ëµÇÁö ¾Ê¾Ò½À´Ï´Ù.',10,1) ROLLBACK TRAN; INSERT INTO userTbl VALUES(N'ABC', N'¿¡ºñ¾¾', 1977, N'¼­¿ï', N'011', N'1111111', 181) /* ------------------------------- <½Ç½À3> p630 ~ p635 ---------------------------------*/ USE master ; RESTORE DATABASE sqlDB FROM DISK ='c:\sqldb.bak' WITH REPLACE ; USE sqlDB; GO CREATE VIEW uv_deliver -- ¹è¼ÛÁ¤º¸¸¦ À§ÇÑ ºä AS SELECT b.userid, u.name, b.prodName, b.price, b.amount, u.addr FROM buyTbl b INNER JOIN userTbl u ON b.userid = u.userid; SELECT * FROM uv_deliver; INSERT INTO uv_deliver VALUES ('DTI', '´çÅÁÀÌ', '±¸µÎ', 50, 1, 'ÀÎõ') CREATE TRIGGER trg_insert ON uv_deliver INSTEAD OF INSERT AS BEGIN INSERT INTO userTbl(userid, name, birthYear, addr) SELECT userid, name, 1900 , addr FROM inserted INSERT INTO buyTbl(userid, prodName, price, amount) SELECT userid, prodName, price, amount FROM inserted END; INSERT INTO uv_deliver VALUES ('SYJ', '»ç¿ëÀÚ', '±¸µÎ', 50, 1, '°­¿ø') SELECT * FROM userTbl WHERE userid = 'DTI'; SELECT * FROM buyTbl WHERE userid = 'DTI'; EXEC sp_helptrigger uv_deliver; EXEC sp_helptext trg_insert; EXEC sp_rename 'dbo.trg_insert', 'dbo.trg_uvInsert' ; DROP TRIGGER dbo.trg_uvInsert; SELECT * FROM sys.sql_modules ; DROP VIEW uv_deliver; /* ------------------------------- <½Ç½À4> p637 ~ p640 ---------------------------------*/ USE master ; RESTORE DATABASE sqlDB FROM DISK ='c:\sqldb.bak' WITH REPLACE ; USE sqlDB; GO CREATE TRIGGER ddlTrg_sqlDB ON DATABASE AFTER DROP_TABLE, ALTER_TABLE AS PRINT ' °æ°í: Å×À̺íÀ» »èÁ¦Çϰųª º¯°æÇÏ´Â °ÍÀº ±ÝÁöµÇ¾î ÀÖ½À´Ï´Ù.' ROLLBACK TRANSACTION; DROP TABLE buyTbl; DROP TABLE userTbl; ALTER TRIGGER ddlTrg_sqlDB ON DATABASE AFTER DDL_TABLE_VIEW_EVENTS AS PRINT ' °æ°í: ¸ðµç Å×À̺í/ºä/À妽º¿¡ ´ëÇÑ »ý¼º,º¯°æ,»èÁ¦´Â ±ÝÁöµÇ¾ú½À´Ï´Ù.' ROLLBACK TRANSACTION; CREATE TABLE prodTbl (prodID INT, prodName NVARCHAR(20)); DISABLE TRIGGER ddlTrg_sqlDB ON DATABASE; ENABLE TRIGGER ddlTrg_sqlDB ON DATABASE; CREATE TRIGGER ddlTrg_server ON ALL SERVER AFTER CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE AS PRINT ' °æ°í: µ¥ÀÌÅͺ£À̽º °ü·ÃµÈ ÀÛ¾÷Àº °ü¸®ÀÚ¿¡°Ô ¹®ÀÇÇϼ¼¿ä.' ROLLBACK TRANSACTION; CREATE DATABASE testDB; DROP TRIGGER ddlTrg_server ON ALL SERVER; USE sqlDB; DROP TRIGGER ddlTrg_sqlDB ON DATABASE; -- ¿©±â¼­ Àá±ñ (P645) EXEC sp_configure 'nested triggers', 0 RECONFIGURE; /* ------------------------------- <½Ç½À5> p644 ~ p650 ---------------------------------*/ USE master; CREATE DATABASE triggerDB; USE triggerDB; CREATE TABLE orderTbl -- ±¸¸Å Å×À̺í (orderNo INT IDENTITY, -- ±¸¸Å ÀϷùøÈ£ userID NVARCHAR(5), -- ±¸¸ÅÇÑ È¸¿ø¾ÆÀ̵ð prodName NVARCHAR(5), -- ±¸¸ÅÇÑ ¹°°Ç orderAmount INT ); -- ±¸¸ÅÇÑ °³¼ö GO CREATE TABLE prodTbl -- ¹°Ç° Å×À̺í ( prodName NVARCHAR(5), -- ¹°°Ç À̸§ account INT ); -- ³²Àº ¹°°Ç¼ö·® GO CREATE TABLE deliverTbl -- ¹è¼Û Å×À̺í ( deliverNo INT IDENTITY, -- ¹è¼Û ÀϷùøÈ£ prodName NVARCHAR(5), -- ¹è¼ÛÇÒ ¹°°Ç amount INT ); -- ¹è¼ÛÇÒ ¹°°Ç°³¼ö INSERT INTO prodTbl VALUES('»ç°ú', 100); INSERT INTO prodTbl VALUES('¹è', 100); INSERT INTO prodTbl VALUES('±Ö', 100); CREATE TABLE recuA (id INT IDENTITY, txt NVARCHAR(10)); -- °£Á¢ Àç±ÍÆ®¸®°Å¿ë Å×À̺íA GO CREATE TABLE recuB (id INT IDENTITY, txt NVARCHAR(10)); -- °£Á¢ Àç±ÍÆ®¸®°Å¿ë Å×À̺íB GO CREATE TABLE recuAA (id INT IDENTITY, txt NVARCHAR(10)); -- Á÷Á¢ Àç±ÍÆ®¸®°Å¿ë Å×À̺íAA EXEC sp_configure 'nested triggers' -- ¹°Ç°Å×ÀÌºí¿¡¼­ °³¼ö °¨¼Ò½ÃŰ´Â Æ®¸®°Å CREATE TRIGGER trg_order ON orderTbl AFTER INSERT AS PRINT '1. trg_order¸¦ ½ÇÇàÇÕ´Ï´Ù.' DECLARE @orderAmount INT DECLARE @prodName NVARCHAR(5) SELECT @orderAmount = orderAmount FROM inserted SELECT @prodName = prodName FROM inserted UPDATE prodTbl SET account = account - @orderAmount WHERE prodName = @prodName ; GO -- ¹è¼ÛÅ×ÀÌºí¿¡ »õ ¹è¼Û °ÇÀ» ÀÔ·ÂÇÏ´Â Æ®¸®°Å CREATE TRIGGER trg_prod ON prodTbl AFTER UPDATE AS PRINT '2. trg_prod¸¦ ½ÇÇàÇÕ´Ï´Ù.' DECLARE @prodName NVARCHAR(5) DECLARE @amount INT SELECT @prodName = prodName FROM inserted SELECT @amount = D.account - I.account FROM inserted I, deleted D -- º¯°æÀüÀǰ³¼ö- º¯°æÈÄÀǰ³¼ö = ÁÖ¹®°³¼ö INSERT INTO deliverTbl(prodName,amount) VALUES(@prodName, @amount); INSERT INTO orderTbl VALUES ('JOHN','¹è',5); SELECT * FROM orderTbl; SELECT * FROM prodTbl; SELECT * FROM deliverTbl; EXEC sp_rename 'dbo.deliverTbl.prodName', 'productName', 'COLUMN'; INSERT INTO orderTbl VALUES ('DANG','»ç°ú',9); SELECT * FROM orderTbl; SELECT * FROM prodTbl; SELECT * FROM deliverTbl; USE triggerDB; SELECT name, is_recursive_triggers_on FROM sys.databases WHERE name = 'triggerDB'; ALTER DATABASE triggerDB SET RECURSIVE_TRIGGERS ON; CREATE TRIGGER trg_recuA ON recuA AFTER INSERT AS DECLARE @id INT SELECT @id = trigger_nestlevel() -- ÇöÀç Æ®¸®°Å ·¹º§°ª PRINT 'Æ®¸®°Å·¹º§==> ' + CAST(@id AS CHAR(5)) INSERT INTO recuB VALUES ('°£Á¢Àç±ÍÆ®¸®°Å') GO CREATE TRIGGER trg_recuB ON recuB AFTER INSERT AS DECLARE @id INT SELECT @id = trigger_nestlevel() -- ÇöÀç Æ®¸®°Å ·¹º§°ª PRINT 'Æ®¸®°Å·¹º§==> ' + CAST(@id AS CHAR(5)) INSERT INTO recuA VALUES ('°£Á¢Àç±ÍÆ®¸®°Å') INSERT INTO recuA VALUES ('óÀ½ÀԷ°ª'); select * from recuA; select * from recuB; ALTER TRIGGER trg_recuA ON recuA AFTER INSERT AS IF ( (SELECT trigger_nestlevel() ) >= 32 ) RETURN DECLARE @id INT SELECT @id = trigger_nestlevel() -- ÇöÀçÆ®¸®°Å·¹º§°ª PRINT 'Æ®¸®°Å·¹º§==> ' + CAST(@id AS CHAR(5)) INSERT INTO recuB VALUES ('°£Á¢Àç±ÍÆ®¸®°Å') GO ALTER TRIGGER trg_recuB ON recuB AFTER INSERT AS IF ( (SELECT trigger_nestlevel() ) >= 32 ) RETURN DECLARE @id INT SELECT @id = trigger_nestlevel() -- ÇöÀçÆ®¸®°Å·¹º§°ª PRINT 'Æ®¸®°Å·¹º§==> ' + CAST(@id AS CHAR(5)) INSERT INTO recuA VALUES ('°£Á¢Àç±ÍÆ®¸®°Å') INSERT INTO recuA VALUES ('óÀ½ÀԷ°ª'); select * from recuA; select * from recuB;