update、insert、delete这些操作的时候,系统会自动调用执行该表上对应的触发器。SQL Server 2005中触发器可以分为两类:DML触发器和DDL触发器,其中DDL触发器它们会影响多种数据定义语言语句而激发,这些语句有create、alter、drop语句。DML触发器分为:1、 after触发器(之后触发)a、 insert触发器b、 update触发器c、 delete触发器2、 instead of 触发器 (之前触发)其中after触发器要求只有执行某一操作insert、update、dele...
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 ] TRIG...
[ @profile_name = ] ‘profile_name‘ ][ , [ @recipients = ] ‘recipients [ ; ...n ]‘ ][ , [ @copy_recipients = ] ‘copy_recipient [ ; ...n ]‘ ][ , [ @blind_copy_recipients = ] ‘blind_copy_recipient [ ; ...n ]‘ ][ , [ @subject = ] ‘subject‘ ] [ , [ @body = ] ‘body‘ ] [ , [ @body_format = ] ‘body_format‘ ][ , [ @importance = ] ‘importance‘ ][ , [ @sensitivity = ] ‘sensitivity‘ ][ , [...
t.name AS TableName, tr.name AS TriggerName, CASE WHEN 1=OBJECTPROPERTY(tr.id, ‘ExecIsTriggerDisabled‘) THEN ‘Disabled‘ ELSE ‘Enabled‘ END Status
FROM sysobjects t
INNER JOIN sysobjects tr
ON t.id = tr.parent_obj
WHERE ( t.xtype = ‘U‘ OR t.XType = ‘V‘)
AND ( tr.xtype = ‘TR‘)
ORDER BY t.name, tr.name
alter table TableName enable trigger TriggerName
a...
----------------------------触发器Trigger---------------------------------2 --快速复制一张表的结构3 select top 0 * into StudentBackUp from Student;4 5 --快速复制一张表和表中的数据6 select * into StudentBackUp2 from Student;7 8 --注意:这两种操作都不会复制表的其他属性,比如索引,主键、外键等等9
10 ----------------------------触发器Trigger开始-----------------------------
11 --从Student快速复制一张...
本文分步骤给大家详细介绍了SQL Server 使用触发器(trigger)发送电子邮件的方法,需要的朋友可以参考下sql 使用系统存储过程 sp_send_dbmail 发送电子邮件语法:sp_send_dbmail [ [ @profile_name = ] profile_name ][ , [ @recipients = ] recipients [ ; ...n ] ][ , [ @copy_recipients = ] copy_recipient [ ; ...n ] ][ , [ @blind_copy_recipients = ] blind_copy_recipient [ ; ...n ] ][ , [ @subject = ] subject ] [ , ...
sql 使用系统存储过程 sp_send_dbmail 发送电子邮件语法:sp_send_dbmail [ [ @profile_name = ] profile_name ][ , [ @recipients = ] recipients [ ; ...n ] ][ , [ @copy_recipients = ] copy_recipient [ ; ...n ] ][ , [ @blind_copy_recipients = ] blind_copy_recipient [ ; ...n ] ][ , [ @subject = ] subject ] [ , [ @body = ] body ] [ , [ @body_format = ] body_format ][ , [ @importance = ] importance ][ , [ @s...
下面的Trigger用于监控存储过程的更改。
创建监控表:
CREATE TABLE AuditStoredProcedures(DatabaseName sysname
, ObjectName sysname
, LoginName sysname
, ChangeDate datetime
, EventType sysname
, EventDataXml xml
);创建监控Trigger:CREATE TRIGGER dbtAuditStoredProcedures
ON DATABASE
FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE
ASDECLARE @eventdata XML;
SET @eventdata = EVENTDATA();INSERT IN...
触发器调用和条件
触发器可以理解为对数据库的某个表进行操作时会自动执行的存储过程,触发器可以指定其被调用的条件,只要满足条件,触发器就会被调用
触发器条件分三种 insert, update, delete
触发器不能主动调用,只能时在数据库执行sql语句时被数据库自动调用
after(For) 与 Instead of 的区别
after(for) 指在sql语句执行完成后再触发,sql语句会正常执行
instead of 在sql语句执行前触发,sql语句不会正常执行,比如要执行i...
use electric
go--变量
--局部变量的声明格式
--declare @局部变量名 数据类型
--局部变量赋值
declare @littlepage int
set @littlepage=18
print cast(@littlepage as varchar(255))declare @little varchar(255)
select @little=warehouse from Stock
print @little--
--存储过程
----if else 语法
--在电力抢修工程数据库中,如果stock表中存在库存量低于1的物资,就显示文本:the amount is not enough;否则显示所有物资信息...