始建触发器,抑制删除数据表中钦定行数据

转自:http://www.maomao365.com/?p=5323

触发器简介:

摘要:
下文主要讲述,如何禁止删除数据表中指定行数据

触发器是一种特殊的存储过程,它的执行不是由程序调用,也不是手动执行,而是由事件来触发。触发器是当对某一个表进行操作。例如:update、insert、delete这些操作的时候,系统会自动调用执行该表上对应的触发器。


触发器分类:

最近收到用户一个需求,禁止所有人删除”表A”中,ID
为1、2、3、4、5的系统默认数据,通过项目组同事仔细研究,我们得到一致的答案为:
在表上新增一个触发器,当删除数据ID为 1 2 3 4
5时候,直接弹出相应的错误,采用这种模式,可以限制所有端口的异常操作(程序
查询分析器),
具体实现方式,如下文所示:

1、DML( 数据操纵语言 Data Manipulation
Language)触发器:是指触发器在数据库中发生 DML
事件时将启用。DML事件是指在表或视图中对数据进行的 insert、update、delete
操作的语句。

 

2、DDL(数据定义语言 Data Definition
Language)触发器:是指当服务器或数据库中发生 DDL
事件时将启用。DDL事件是指在表或索引中的 create、alter、drop 操作语句。


3、登陆触发器:是指当用户登录 SQL SERVER
实例建立会话时触发。如果身份验证失败,登录触发器不会触发。

create trigger trTriggerA
on A
for delete
as
set nocount on
if exists (select 1 from deleted where id in (1,2,3,4,5) )
begin
RAISERROR ('禁止删除系统记录 1 2 3 4 5 ', 16, 1)
ROLLBACK TRANSACTION
end

其中 DML 触发器比较常用,根据 DML
触发器触发的方式不同又分为以下两种情况:

 

after 触发器(之后触发):其中 after 触发器要求只有执行
insert、update、delete 某一操作之后触发器才会被触发,且只能定义在表上。

instead of 触发器 (之前触发):instead of
触发器并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身。可以在表或视图上定义
instead of 触发器。

DML
触发器有两个特殊的表:插入表(instered)和删除表(deleted),这两张表是逻辑表。这两个表是建立在数据库服务器的内存中,而且两张表的都是只读的。这两张表的结构和触发器所在的数据表的结构是一样的。当触发器完成工作后,这两张表就会被删除。Inserted
表的数据是插入或是修改后的数据,而 deleted
表的数据是更新前的或是已删除的数据。

图片 1

AFTER 触发器语法:

 1 CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
 2 ON { table }   
 3 [ WITH <dml_trigger_option> [ ,...n ] ]  
 4 { FOR | AFTER }   
 5 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
 6 AS { sql_statement  [ ; ] [ ,...n ] }  
 7 
 8 <dml_trigger_option> ::=  
 9     [ NATIVE_COMPILATION ]  
10     [ SCHEMABINDING ]  
11     [ EXECUTE AS Clause ]

INSTEAD OF 触发器语法:

 1 CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name   
 2 ON { table | view }   
 3 [ WITH <dml_trigger_option> [ ,...n ] ]  
 4 { FOR | AFTER | INSTEAD OF }   
 5 { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }   
 6 [ WITH APPEND ]  
 7 [ NOT FOR REPLICATION ]   
 8 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME <method specifier [ ; ] > }  
 9 
10 <dml_trigger_option> ::=  
11     [ ENCRYPTION ]  
12     [ EXECUTE AS Clause ]  
13 
14 <method_specifier> ::=  
15     assembly_name.class_name.method_name  

DDL 触发器语法:

1 CREATE [ OR ALTER ] TRIGGER trigger_name   
2 ON { ALL SERVER | DATABASE }   
3 [ WITH <ddl_trigger_option> [ ,...n ] ]  
4 { FOR | AFTER } { event_type | event_group } [ ,...n ]  
5 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
6 
7 <ddl_trigger_option> ::=  
8     [ ENCRYPTION ]  
9     [ EXECUTE AS Clause ]  

登陆触发器语法:

1 CREATE [ OR ALTER ] TRIGGER trigger_name   
2 ON ALL SERVER   
3 [ WITH <logon_trigger_option> [ ,...n ] ]  
4 { FOR| AFTER } LOGON    
5 AS { sql_statement  [ ; ] [ ,...n ] | EXTERNAL NAME < method specifier >  [ ; ] }  
6 
7 <logon_trigger_option> ::=  
8     [ ENCRYPTION ]  
9     [ EXECUTE AS Clause ]  

参数:

CREATE OR ALTER:

创建或者有条件的修改触发器(即要修改的触发器必须已经存在)。

schema_name:
DML触发器所属的模式的名称(即所有者,例如:dbo)。

trigger_name:
是触发器的名称。

table | view:

是执行 DML
触发器的表或视图,有时称为触发器表或触发器视图。指定表格或视图的完全限定名称是可选的。视图只能由
INSTEAD OF 触发器引用。

DATABASE:
将 DDL
触发器的范围应用于当前数据库。如果指定,触发器会在当前数据库中发生 event_type 或 event_group 时触发。

ALL SERVER:

将 DDL
或登录触发器的作用域应用于当前服务器。如果指定,触发器会在当前服务器的任何地方发生 event_type 或 event_group 时触发。

WITH ENCRYPTION:

加密 CREATE TRIGGER 语句的文本。使用 WITH ENCRYPTION 可以防止触发器作为
SQL Server 复制的一部分进行发布。无法为 CLR 触发器指定 WITH ENCRYPTION。

EXECUTE AS:
指定执行触发器的安全上下文。以便能够控制 SQL Server
实例用于验证触发器引用的任何数据库对象的权限的用户帐户。

NATIVE_COMPILATION:
表示触发器是本地编译的。

SCHEMABINDING:
指定触发器引用的表不能被删除或更改。

FOR | AFTER:
AFTER 指定仅在触发 SQL 语句中指定的所有操作成功执行时触发 DML
触发器。所有引用级联操作和约束检查在此触发器触发之前也必须成功。当 FOR
是指定的唯一关键字时,AFTER 是默认值。视图无法定义AFTER触发器。

INSTEAD OF:
指定执行 DML 触发器而不是触发 SQL 语句,因此覆盖触发语句的操作。无法为
DDL 或登录触发器指定 INSTEAD OF。

对于 INSTEAD OF 触发器,在具有指定级联动作 ON DELETE
的引用关系的表上不允许使用 DELETE 选项。类似地,在具有指定级联动作 ON UPDATE
的引用关系的表上,不允许 UPDATE 选项。

{[DELETE] [,] [INSERT] [,] [UPDATE]} :
指定在针对此表或视图进行尝试时激活 DML
触发器的数据修改语句。必须至少指定一个选项。在触发器定义中允许以任何顺序对这些选项进行任意组合。

event_type:
是执行后导致 DDL 触发器触发的 Transact-SQL 语言事件的名称。

event_group:
是 Transact-SQL 语言事件的预定义分组的名称。属于任何 Transact-SQL
语言事件执行后的 DDL 触发器触发 event_group。

sql_statement:
是触发条件和动作。触发条件指定附加条件,以确定尝试的 DML,DDL
或登录事件是否导致执行触发器操作。

<method_specifier>:

对于 CLR
触发器,指定要与触发器绑定的程序集的方法。该方法不得不引用任何参数并返回
void。class_name 必须是有效的 SQL Server
标识符,并且必须作为具有程序集可见性的程序集中的类存在。

发表评论

电子邮件地址不会被公开。 必填项已用*标注