Sql Server中与触发器相关的Sql语句模板

创建时间：2012年01月04日 15:03 阅读次数：(7168)

分享到：

以下是利用sql语句对触发器进行新增，修改，删除的语句模板，来源于msdn。具体语法约定请参考下面链接：
Transact-SQL 语法约定 (Transact-SQL)

创建触发器sql语句模板：

--======================================
--  Create T-SQL Trigger Template
--======================================
USE <database_name, sysname, AdventureWorks >
GO

IF OBJECT_ID ('<schema_name, sysname, Sales >.<trigger_name, sysname, uStore >','TR') IS NOT NULL
   DROP TRIGGER <schema_name, sysname, Sales >.<trigger_name, sysname, uStore >
GO

CREATE TRIGGER <schema_name, sysname, Sales >.<trigger_name, sysname, uStore >
   ON  <schema_name, sysname, Sales >.<table_name, sysname, Store >
   AFTER <data_modification_statements, , UPDATE >
AS <T-SQL_statement, , UPDATE Sales.Store SET ModifiedDate = GETDATE() FROM inserted WHERE inserted.CustomerID = Sales.Store.CustomerID >
GO

-- ================================================
-- Template generated from Template Explorer using:
-- Create Trigger (New Menu).SQL
--
-- Use the Specify Values for Template Parameters
-- command (Ctrl-Shift-M) to fill in the parameter
-- values below.
--
-- See additional Create Trigger templates for more
-- examples of different Trigger statements.
--
-- This block of comments will not be included in
-- the definition of the function.
-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name >
-- Create date: <Create Date,, >
-- Description: <Description,, >
-- =============================================
CREATE TRIGGER <Schema_Name, sysname, Schema_Name >.<Trigger_Name, sysname, Trigger_Name >
   ON  <Schema_Name, sysname, Schema_Name >.<Table_Name, sysname, Table_Name >
   AFTER <Data_Modification_Statements, , INSERT,DELETE,UPDATE >
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

    -- Insert statements for trigger here

END
GO

修改触发器sql语句模板：

--======================================
-- Alter T-SQL Trigger Template
--======================================
USE <database_name, sysname, AdventureWorks >
GO

ALTER TRIGGER <schema_name, sysname, Sales >.<trigger_name, sysname, uStore >
ON <schema_name, sysname, Sales >.<table_name, sysname, Store >
AFTER <data_modification_statements, , UPDATE >
AS <T-SQL_statement, , UPDATE Sales.Store SET ModifiedDate = GETDATE() FROM inserted WHERE inserted.CustomerID = Sales.Store.CustomerID >
GO

删除触发器sql语句模板：

--======================================
-- Drop Trigger Template
--======================================
USE <database_name, sysname, AdventureWorks >
GO

IF OBJECT_ID ('<schema_name, sysname, Sales >.<trigger_name, sysname, uStore >','TR') IS NOT NULL
DROP TRIGGER <schema_name, sysname, Sales >.<trigger_name, sysname, uStore >
GO

来源：
说明：所有来源为 .net学习网的文章均为原创，如有转载，请在转载处标注本页地址，谢谢！

【编辑：Wyf】

打赏