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
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)