使用触发器将数据从一个数据库插入到另一个数 [英] Insert data from one database to another database using trigger

问题描述

我想使用触发器将一个数据库表值插入另一个数据库表值。

触发后，它会自动插入到另一个数据库表中。第一个数据库表状态也会在触发后更新触发。



我尝试过：

I want to insert one database table value to another database table value using trigger..
After Trigger fire it will automatically inserted to another database table.and the first database table status also updated after fire trigger.

What I have tried:

create TRIGGER AfterInsert ON DEMODATABSE.[dbo].[TRANS]       
FOR INSERT
AS
BEGIN
  DECLARE @LBrCode INT ;
  DECLARE @EntryDate DATETIME ;
  DECLARE @PrdAcctId VARCHAR(32);
  DECLARE @AgtPrdAcctId VARCHAR(32);
  DECLARE @AMOUNT FLOAT;
  DECLARE @Type VARCHAR(10);
  --SET @Type='Credit'
  SELECT @Type = T.TYPE FROM [SYSDB01].DEMODATABSE.[dbo].[TRANS] T;

  IF @Type = 'Credit'
  BEGIN
    SET NOCOUNT ON
    SET @LBrCode=5;
    SET @EntryDate=getdate();
    SELECT @EntryDate=T.DATE FROM [SYSDB01].DEMODATABSE.[dbo].[TRANS] T;  
    SELECT  @PrdAcctId =G.PrdAcctId FROM [SYSDB01].DEMODATABSE.dbo.[GL] G WHERE G.PrdAcctId IS NOT NULL 
    SELECT  @AgtPrdAcctId =G.AgtPrdAcctId FROM [SYSDB01].DEMODATABSE.dbo.[GL] G;
    SELECT @AMOUNT =T.AMOUNT FROM  [SYSDB01].DEMODATABSE.[dbo].[TRANS] T; 


    INSERT INTO [SYSDB01].SECONDDEMODATABSE.dbo.testtable( LBrCode , EntryDate, CustPrdAcctId, AgtPrdAcctId, CustAmt)     
   
   VALUES (5,getdate(),@PrdAcctId,@AgtPrdAcctId,@AMOUNT)  
   
     
    SELECT @LBrCode,T.DATE, G.PrdAcctId,G.AgtPrdAcctId, T.AMOUNT   
      FROM  [SYSDB01].DEMODATABSE.dbo.TRANS T ,[SYSDB01].DEMODATABSE.dbo.GL G    
      WHERE  
       T.TYPE='Credit' 
    AND
    	   G.AC_NO = T.AC_NO 
	 	 AND G.PrdAcctId IS NOT NULL
  	     AND T.status = '5'

	 ----------- --------- ---------------
    UPDATE a SET a.status ='6' from [SYSDB01].DEMODATABSE.dbo.[TRANS] a
    INNER JOIN [SYSDB01].DEMODATABSE.dbo.[GL] b
		ON a.AC_NO=b.AC_NO 
    WHERE   
	  a.TYPE='Credit' 
    AND b.AC_NO = a.AC_NO 
    AND a.status='5'
 
	PRINT 'AFTER INSERT trigger fired.' 
 END 
END
GO

推荐答案

创建触发器user_bi在用户插入每个行之前

BEGIN

INSERT INTO remote_user（ID，name，age）VALUES（NEW.ID ，NEW.name，NEW.Age）;

END

CREATE TRIGGER user_bi BEFORE INSERT ON user FOR EACH ROW
BEGIN
INSERT INTO remote_user (ID,name, age) VALUES (NEW.ID,NEW.name, NEW.Age);
END

CREATE TRIGGER user_bu BEFORE每个行的用户更新

BEGIN

UPDATE remote_user

SET ID = NEW.ID，

name = NEW.name

age = NEW.Age

WHERE ID = OLD.ID;

END

CREATE TRIGGER user_bu BEFORE UPDATE ON user FOR EACH ROW
BEGIN
UPDATE remote_user
SET ID= NEW.ID,
name= NEW.name
age = NEW.Age
WHERE ID = OLD.ID;
END

创建TRIGGER user_bd之前删除用户每个行

BEGIN

DELETE from remote_user

WHERE ID = OLD.ID;

END

CREATE TRIGGER user_bd BEFORE DELETE ON user FOR EACH ROW
BEGIN
DELETE FROM remote_user
WHERE ID= OLD.ID;
END

这篇关于使用触发器将数据从一个数据库插入到另一个数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！