如何在数据库插入上创建触发器 [英] How to create trigger on database insert
本文介绍了如何在数据库插入上创建触发器的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
嗨
我需要这样的触发器,以便在记录本身插入时在其他数据库上插入记录.
Hi
I need such trigger that insert record on other database when record inserted in itself.
推荐答案
USE [ZCLS_MM]
GO
/******对象:触发器[dbo].[TriggerOnInsert]脚本日期:09/19/2012 11:12:28 ******/
设置ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-================================================ =========
-作者:Ravi Sharma
-创建日期:2012年10月10日
-说明:触发以自动插入到其他数据库中
-================================================ =========
ALTER TRIGGER [dbo].[TriggerOnInsert]
开启[ZCLS_MM].[dbo].[MessageTransaction]
插入
AS
开始
选择不同的i.MessageID,i.ChannelNo,i.ExtensionNo,i.IPAddress,i.MessageFileName,i.MessageDate,i.CallerId,i.InOutFlag,
i.PlayPath,i.Information from插入的i
声明
@MessageID数字(10,0),
@ChannelNo数字(3,0),
@ExtensionNo nvarchar(50),
@IPAddress nvarchar(50),
@MessageFileName nvarchar(255),
@MessageDate日期时间,
@CallerId nvarchar(50),
@InOutFlag int,
@PlayPath nvarchar(255),
@信息char(25)
从插入的i中选择@ ChannelNo = i.ChannelNo;
从插入的i中选择@ ExtensionNo = i.ExtensionNo;
从插入的i中选择@ IPAddress = i.IPAddress;
从插入的i中选择@ MessageFileName = i.MessageFileName;
从插入的i中选择@ MessageDate = i.MessageDate;
从插入的i中选择@ CallerId = i.CallerId;
从插入的i中选择@ InOutFlag = i.InOutFlag;
从插入的i中选择@ PlayPath = i.PlayPath;
从插入的i中选择@ Information = i.Information;
插入ZCLS.dbo.MessageTransaction(ChannelNo,ExtensionNo,IPAddress,MessageFileName,MessageDate,CallerId,InOutFlag,PlayPath,Information)值
(@ ChannelNo,@ ExtensionNo,@ IPAddress,@ MessageFileName,@ MessageDate,@ CallerId,@ InOutFlag,@ PlayPath,@ Information)
打印触发了AFTER INSERT触发器."
END
USE [ZCLS_MM]
GO
/****** Object: Trigger [dbo].[TriggerOnInsert] Script Date: 09/19/2012 11:12:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =========================================================
-- Author: Ravi Sharma
-- Create date: 10/10/2012
-- Description: Trigger to auto insert into other database
-- =========================================================
ALTER TRIGGER [dbo].[TriggerOnInsert]
ON [ZCLS_MM].[dbo].[MessageTransaction]
FOR INSERT
AS
BEGIN
select distinct i.MessageID,i.ChannelNo,i.ExtensionNo,i.IPAddress,i.MessageFileName,i.MessageDate,i.CallerId,i.InOutFlag,
i.PlayPath,i.Information from inserted i
declare
@MessageID numeric(10, 0),
@ChannelNo numeric(3, 0),
@ExtensionNo nvarchar(50),
@IPAddress nvarchar(50),
@MessageFileName nvarchar(255),
@MessageDate datetime,
@CallerId nvarchar(50),
@InOutFlag int,
@PlayPath nvarchar(255),
@Information char(25)
select @ChannelNo=i.ChannelNo from inserted i;
select @ExtensionNo=i.ExtensionNo from inserted i;
select @IPAddress=i.IPAddress from inserted i;
select @MessageFileName=i.MessageFileName from inserted i;
select @MessageDate=i.MessageDate from inserted i;
select @CallerId=i.CallerId from inserted i;
select @InOutFlag=i.InOutFlag from inserted i;
select @PlayPath=i.PlayPath from inserted i;
select @Information=i.Information from inserted i;
Insert into ZCLS.dbo.MessageTransaction(ChannelNo,ExtensionNo,IPAddress,MessageFileName,MessageDate,CallerId,InOutFlag,PlayPath,Information) values
(@ChannelNo,@ExtensionNo,@IPAddress,@MessageFileName,@MessageDate,@CallerId,@InOutFlag,@PlayPath,@Information)
PRINT ''AFTER INSERT trigger fired.''
END
这篇关于如何在数据库插入上创建触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文