20190422 T-SQL 触发器

-- 1 数据库服务

-- 2 触发器

CREATE TRIGGER no_insert
on xs
AFTER INSERT
AS
BEGIN
RAISERROR('XS不让插入数据',1,1);
ROLLBACK TRANSACTION
END

SELECT *
FROM XS WHERE 学号='14311011'

INSERT INTO xs(学号,姓名,总学分)
values('14311011','soar1','20')


EXEC Sp_helptext no_insert -- sys.sp_ -- 触发器的查看内容 -- 触发器的启用和禁用 -- 删除 DROP TRIGGER no_insert --重命名 EXEC sp_rename no_insert,new_insert; -- 禁用和启用触发器,语句 ALTER TABLE XS DISABLE TRIGGER new_insert ALTER TABLE XS ENABLE TRIGGER new_insert -- INSERT -- DELETE SELECT *
FROM XS WHERE 学号='14311011' -- DELETE FROM XS WHERE 学号='14311011'; -- 1 CREATE TRIGGER xs_del ON XS AFTER DELETE AS BEGIN SELECT 学号 AS '被删除的学生',姓名 FROM deleted END -- UPDATE 更新 ,更新为 test SELECT *
FROM XS WHERE 学号='14311011' UPDATE XS SET 姓名='soar' WHERE 学号='14311011' SELECT LEN('soar.pang') -- CREATE TRIGGER XS_UPDATE ON XS AFTER UPDATE AS BEGIN SELECT 姓名 as '更改后',学号 FROM inserted; SELECT 姓名 as '更新前',学号 FROM deleted; END -- 真的数据库的触发器 CREATE TRIGGER safty ON DATABASE -- ALL SERVER 表 FOR DROP_TABLE,ALTER_TABLE -- AFTER AS BEGIN PRINT '当前数据库禁止更新删除动作' --并更 ROLLBACK TRANSACTION END DROP TABLE Test_2019_02; -- 表级、服务器、数据库级别 -- 级联 CREATE TRIGGER trigdel ON XS AFTER DELETE AS BEGIN DELETE XK WHERE 学号=(SELECT 学号 from deleted) END SELECT * FROM XS where 学号='14311002' SELECT * from xk where 学号='14311002' -- inner join -- where DELETE XS WHERE 学号='14311002' -- 索引 -- 第一个索引 位置

posted @ 2019-04-22 10:34  soar.pang  阅读(129)  评论(0编辑  收藏  举报