读书人

这个语法怎么理解

发布时间: 2013-06-26 14:29:32 作者: rapoo

这个语法如何理解?

-- =============================================
-- Create basic Instead Of Trigger
-- =============================================
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'<trigger_name, sysname, trig_test>'
AND type = 'TR')
DROP TRIGGER <trigger_name, sysname, trig_test>
GO

CREATE TRIGGER <trigger_name, sysname, trig_test>
ON <table_or_view_name, sysname, pubs.dbo.sales>
INSTEAD OF INSERT
AS
BEGIN
RAISERROR (50009, 16, 10)
EXEC sp_who
END
GO


请问<trigger_name, sysname, trig_test>这个语法如何理解:<,>,trigger_name,sysname各代表什么?谢谢! SQL
[解决办法]
单从字面意思看,trigger_name:触发器名、sysname:系统名
触发器语法:
Trigger on an INSERT, UPDATE, or DELETE statement to a table or view (DML Trigger)
CREATE TRIGGER [ schema_name . ]trigger_name
ON { table
[解决办法]
view }
[ WITH <dml_trigger_option> [ ,...n ] ]
{ FOR
[解决办法]
AFTER
[解决办法]
INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
[ WITH APPEND ]
[ NOT FOR REPLICATION ]
AS { sql_statement [ ; ] [ ,...n ]
[解决办法]
EXTERNAL NAME <method specifier [ ; ] > }

<dml_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

<method_specifier> ::=
assembly_name.class_name.method_name

Trigger on a CREATE, ALTER, DROP, GRANT, DENY, REVOKE, or UPDATE STATISTICS statement (DDL Trigger)
CREATE TRIGGER trigger_name
ON { ALL SERVER
[解决办法]
DATABASE }
[ WITH <ddl_trigger_option> [ ,...n ] ]
{ FOR
[解决办法]
AFTER } { event_type
[解决办法]
event_group } [ ,...n ]
AS { sql_statement [ ; ] [ ,...n ]


[解决办法]
EXTERNAL NAME < method specifier > [ ; ] }

<ddl_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

<method_specifier> ::=
assembly_name.class_name.method_name

Trigger on a LOGON event (Logon Trigger)
CREATE TRIGGER trigger_name
ON ALL SERVER
[ WITH <logon_trigger_option> [ ,...n ] ]
{ FOR
[解决办法]
AFTER } LOGON
AS { sql_statement [ ; ] [ ,...n ]
[解决办法]
EXTERNAL NAME < method specifier > [ ; ] }
<logon_trigger_option> ::=
[ ENCRYPTION ]
[ EXECUTE AS Clause ]

<method_specifier> ::=
assembly_name.class_name.method_name


[解决办法]


-- ============================================= -- Create basic Instead Of Trigger -- =============================================
--判断是表中是否存在触发器
-- N'<trigger_name, sysname, trig_test>': 触发器名称
IF EXISTS (SELECT name FROM sysobjects WHERE name = N'<trigger_name, sysname, trig_test>'
AND type = 'TR')
--如果存在则删除该触发器
DROP TRIGGER <trigger_name, sysname, trig_test>
GO
--创建触发器
-- <trigger_name, sysname, trig_test> :触发器名称
-- <table_or_view_name, sysname, pubs.dbo.sales> :创建那张表或视图的触发器
CREATE TRIGGER <trigger_name, sysname, trig_test> ON <table_or_view_name, sysname, pubs.dbo.sales>
--插入时触发
INSTEAD OF INSERTASBEGIN
AS
BEGIN
--抛出一个错误
RAISERROR (50009, 16, 10)
--sp_who可以返回如下信息: (可选参数LoginName, 或active代表活动会话数)
--Spid (系统进程ID)
--
--status (进程状态)
--
--loginame (用户登录名)
--
--hostname(用户主机名)
--
--blk (阻塞进程的SPID)
--
--dbname (进程正在使用的数据库名)
--
--Cmd (当前正在执行的命令类型)

EXEC sp_who
END

读书人网 >SQL Server

热点推荐