表
表名:属性表二
DML触发器
DELETE (AFTER)触发器
格式:
CREATE TRIGGER 触发器名
ON 表名
AFTER DELETE
AS
BEGIN
DECLARE @A(参数) INT
SELECT @A=DELETED.列名 FROM DELETED
DELETE 表名
where 条件
END
例:
CREATE TRIGGER chufaqi
ON [dbo].[属性表二]
AFTER DELETE
AS
BEGIN
DECLARE @A INT
SELECT @A=DELETED.ID FROM DELETED
DELETE 属性表
where id=@A
END
意思为如果删除id列的某一值,则会直接删除这一行的所有值
做DELETE 操作
执行SQL
INSTEAD OF 触发器
例:
CREATE TRIGGER chufaqi2
ON [dbo].[属性表二]
INSTEAD OF DELETE
AS
BEGIN
DECLARE @B INT
SELECT @B=COUNT(*) FROM DELETED
IF @B>0
BEGIN
RAISERROR ('不能删除',10,1)
rollback transaction
end
end
不允许进行删除操作的触发器
結果:
UPDATE 触发器
实例:
CREATE TRIGGER trigger_update
on [dbo].[BAS]
after update
as
if update(ID)
begin
update [dbo].[班级表]
set ID=(SELECT ID FROM inserted)
WHERE ID=(SELECT ID FROM deleted)
END
效果:如果更改了BAS表的ID,班级和BAS表的ID都会被修改
执行前的两个表:
BAS:
班級表:
执行sql语句:
UPDATE [dbo].[BAS]
SET ID=111 WHERE ID=1
触发器的删除
格式:
DROP TRIGGER 触发器名
INISERT 触发器
实例:如果没有与另一个表列里的内容相同的值,无法插入
CREATE TRIGGER chufaqi4
ON [dbo].[BAS]
AFTER INSERT
AS
BEGIN TRANSACTION/*事务*/
IF EXISTS(SELECT * FROM INSERTED c
WHERE c.[name] not in(SELECT [name] FROM [dbo].[属性表]))
BEGIN
RAISERROR ('ERROR',16,1)/*报错*/
ROLLBACK TRANSACTION
END
ELSE
COMMIT TRANSACTION/*提交事务*/
DDL 触发器
防止表被修改或者删除
实例:
CREATE TRIGGER trigger_ddl1
on database
for drop_table ,alter_table
as
print '不能修改'
rollback
测试:
drop table [dbo].[BAS]
結果:
删除DDL触发器
格式:
DROP TRIGGER 触发器名
ON DATABASE