June 30, 2009 at 1:12 am
Hi,
I want to use triggers to make changes to another database after we insert or update a record. The table name is same
for ex. FIELD_EXECUTIVE_DETAILS table there in Test1 Database and FIELD_EXECUTIVE_DETAILS in Test2 database. All the columns are same in both the databases.
How can i add a trigger to test1 database for field_executive_details so that the same data gets changed/ inserted in test2 database.
Thank you,
Syed
June 30, 2009 at 5:52 am
syedtameem11 (6/30/2009)
Hi,I want to use triggers to make changes to another database after we insert or update a record. The table name is same
for ex. FIELD_EXECUTIVE_DETAILS table there in Test1 Database and FIELD_EXECUTIVE_DETAILS in Test2 database. All the columns are same in both the databases.
How can i add a trigger to test1 database for field_executive_details so that the same data gets changed/ inserted in test2 database.
Thank you,
Syed
create linked server and use 4 part notation while referring the table from Test2 db.
check out sp_addlinkedserver in BOL
June 30, 2009 at 7:28 am
i think the original poster wanted a different database, not necessarily a different server, right?
a simple trigger like:
CREATE TRIGGER TR_MyTrigger On SomeTable
FOR UPDATE
AS
BEGIN
UPDATE MyAlias
SET col1 = INSERTED.col1,
col2=INSERTED.col2
...
FROM INSERTED
INNER JOIN OTHERDatabase.dbo.SomeTable MyAlias --3 part naming, 4 part if a linked server
ON INSERTED.PKID = MyAlias.PKId
END
Lowell
June 30, 2009 at 8:18 am
Oh Yes. Thanks for correcting me Lowell. In such case we dont need to create a linked server.
June 30, 2009 at 10:59 pm
Thanks,
I have got the solution.
After Insert statement trigger:
CREATE TRIGGER Field_Executive_Trigger
ON FIELD_EXECUTIVE_DETAILS
FOR INSERT
AS
BEGIN
DECLARE @FIELD_EXECUTIVE_ENROLLMENT_NO INT
SET @FIELD_EXECUTIVE_ENROLLMENT_NO = (SELECT FIELD_EXECUTIVE_ENROLLMENT_NO FROM inserted)
DECLARE @BRANCH_ID INT
SET @BRANCH_ID = (SELECT BRANCH_ID FROM inserted)
DECLARE @AGENT_DESIGN_ID INT
SET @AGENT_DESIGN_ID = (SELECT AGENT_DESIGN_ID FROM inserted)
DECLARE @DATE_OF_ENROLLEMENT DATETIME
SET @DATE_OF_ENROLLEMENT = (SELECT DATE_OF_ENROLLEMENT FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_NAME VARCHAR(50)
SET @FIELD_EXECUTIVE_NAME = (SELECT FIELD_EXECUTIVE_NAME FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_AGE FLOAT
SET @FIELD_EXECUTIVE_AGE = (SELECT FIELD_EXECUTIVE_AGE FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_DOB DATETIME
SET @FIELD_EXECUTIVE_DOB = (SELECT FIELD_EXECUTIVE_DOB FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_RELATION CHAR(10)
SET @FIELD_EXECUTIVE_RELATION = (SELECT FIELD_EXECUTIVE_RELATION FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_RELATION_NAME VARCHAR(50)
SET @FIELD_EXECUTIVE_RELATION_NAME = (SELECT FIELD_EXECUTIVE_RELATION_NAME FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_ADDRESS VARCHAR(500)
SET @FIELD_EXECUTIVE_ADDRESS = (SELECT FIELD_EXECUTIVE_ADDRESS FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_DISTRICT VARCHAR(50)
SET @FIELD_EXECUTIVE_DISTRICT = (SELECT FIELD_EXECUTIVE_DISTRICT FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_STATE_ID INT
SET @FIELD_EXECUTIVE_STATE_ID = (SELECT FIELD_EXECUTIVE_STATE_ID FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_PIN FLOAT
SET @FIELD_EXECUTIVE_PIN = (SELECT FIELD_EXECUTIVE_PIN FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_PHONE_NO VARCHAR(50)
SET @FIELD_EXECUTIVE_PHONE_NO = (SELECT FIELD_EXECUTIVE_PHONE_NO FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_CELL_NO VARCHAR(50)
SET @FIELD_EXECUTIVE_CELL_NO = (SELECT FIELD_EXECUTIVE_CELL_NO FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_PAN_NO VARCHAR(50)
SET @FIELD_EXECUTIVE_PAN_NO = (SELECT FIELD_EXECUTIVE_PAN_NO FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_PASSPORT_NO VARCHAR(50)
SET @FIELD_EXECUTIVE_PASSPORT_NO = (SELECT FIELD_EXECUTIVE_PASSPORT_NO FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_BANK_AC_NO VARCHAR(50)
SET @FIELD_EXECUTIVE_BANK_AC_NO = (SELECT FIELD_EXECUTIVE_BANK_AC_NO FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_BANK_NAME VARCHAR(50)
SET @FIELD_EXECUTIVE_BANK_NAME = (SELECT FIELD_EXECUTIVE_BANK_NAME FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_BANK_PLACE VARCHAR(50)
SET @FIELD_EXECUTIVE_BANK_PLACE = (SELECT FIELD_EXECUTIVE_BANK_PLACE FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_PHOTO_PATH VARCHAR(500)
SET @FIELD_EXECUTIVE_PHOTO_PATH = (SELECT FIELD_EXECUTIVE_PHOTO_PATH FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_SIGNATURE_PATH VARCHAR(500)
SET @FIELD_EXECUTIVE_SIGNATURE_PATH = (SELECT FIELD_EXECUTIVE_SIGNATURE_PATH FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_SPOUSE_NAME VARCHAR(50)
SET @FIELD_EXECUTIVE_SPOUSE_NAME = (SELECT FIELD_EXECUTIVE_SPOUSE_NAME FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_SPOUSE_RELATION VARCHAR(50)
SET @FIELD_EXECUTIVE_SPOUSE_NAME = (SELECT FIELD_EXECUTIVE_SPOUSE_NAME FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_SPOUSE_AGE FLOAT
SET @FIELD_EXECUTIVE_SPOUSE_AGE = (SELECT FIELD_EXECUTIVE_SPOUSE_AGE FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_SPOUSE_DOB DATETIME
SET @FIELD_EXECUTIVE_SPOUSE_DOB = (SELECT FIELD_EXECUTIVE_SPOUSE_DOB FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_NO_OF_CHILDREN INT
SET @FIELD_EXECUTIVE_NO_OF_CHILDREN = (SELECT FIELD_EXECUTIVE_NO_OF_CHILDREN FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_NO_OF_MALE_CHILDREN INT
SET @FIELD_EXECUTIVE_NO_OF_MALE_CHILDREN = (SELECT FIELD_EXECUTIVE_NO_OF_MALE_CHILDREN FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_NO_OF_FEMALE_CHILDREN INT
SET @FIELD_EXECUTIVE_NO_OF_FEMALE_CHILDREN = (SELECT FIELD_EXECUTIVE_NO_OF_FEMALE_CHILDREN FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_FATHERS_NAME VARCHAR(50)
SET @FIELD_EXECUTIVE_FATHERS_NAME = (SELECT FIELD_EXECUTIVE_FATHERS_NAME FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_FATHERS_AGE FLOAT
SET @FIELD_EXECUTIVE_FATHERS_AGE = (SELECT FIELD_EXECUTIVE_FATHERS_AGE FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_FATHERS_DOB DATETIME
SET @FIELD_EXECUTIVE_FATHERS_DOB = (SELECT FIELD_EXECUTIVE_FATHERS_DOB FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_FATHERS_STATUS CHAR(1)
SET @FIELD_EXECUTIVE_FATHERS_STATUS = (SELECT FIELD_EXECUTIVE_FATHERS_STATUS FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_MOTHERS_NAME VARCHAR(50)
SET @FIELD_EXECUTIVE_MOTHERS_NAME =(SELECT FIELD_EXECUTIVE_MOTHERS_NAME FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_MOTHERS_AGE FLOAT
SET @FIELD_EXECUTIVE_MOTHERS_AGE = (SELECT FIELD_EXECUTIVE_MOTHERS_AGE FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_MOTHERS_DOB DATETIME
SET @FIELD_EXECUTIVE_MOTHERS_DOB = (SELECT FIELD_EXECUTIVE_MOTHERS_DOB FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_MOTHERS_STATUS CHAR(1)
SET @FIELD_EXECUTIVE_MOTHERS_STATUS = (SELECT FIELD_EXECUTIVE_MOTHERS_STATUS FROM INSERTED)
DECLARE @INTRO_FIELD_EXECUTIVE_ID BIGINT
SET @INTRO_FIELD_EXECUTIVE_ID = (SELECT INTRO_FIELD_EXECUTIVE_ID FROM INSERTED)
DECLARE @DELETESTATUS INT
SET @DELETESTATUS = (SELECT DELETESTATUS FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_ID BIGINT
SET @FIELD_EXECUTIVE_ID = (SELECT FIELD_EXECUTIVE_ID FROM INSERTED)
DECLARE @userid VARCHAR(10)
SET @userid = (SELECT USERID FROM INSERTED)
DECLARE @CREATEDDATE DATETIME
SET @CREATEDDATE = (SELECT CREATEDDATE FROM INSERTED)
DECLARE @MODIFIEDDATE DATETIME
SET @MODIFIEDDATE = (SELECT MODIFIEDDATE FROM INSERTED)
DECLARE @PRINTED_ID INT
SET @PRINTED_ID = (SELECT PRINTED_ID FROM INSERTED)
DECLARE @Activate INT
SET @Activate = (SELECT Activate FROM INSERTED)
INSERT INTO [SKF_INSURANCE].[dbo].FIELD_EXECUTIVE_DETAILS
(FIELD_EXECUTIVE_ENROLLMENT_NO,BRANCH_ID,AGENT_DESIGN_ID,
DATE_OF_ENROLLEMENT,FIELD_EXECUTIVE_NAME,FIELD_EXECUTIVE_AGE,
FIELD_EXECUTIVE_DOB,FIELD_EXECUTIVE_RELATION,FIELD_EXECUTIVE_RELATION_NAME,
FIELD_EXECUTIVE_ADDRESS,FIELD_EXECUTIVE_DISTRICT,
FIELD_EXECUTIVE_STATE_ID,FIELD_EXECUTIVE_PIN,
FIELD_EXECUTIVE_PHONE_NO,FIELD_EXECUTIVE_CELL_NO,
FIELD_EXECUTIVE_PAN_NO,FIELD_EXECUTIVE_PASSPORT_NO,
FIELD_EXECUTIVE_BANK_AC_NO,FIELD_EXECUTIVE_BANK_NAME,
FIELD_EXECUTIVE_BANK_PLACE,FIELD_EXECUTIVE_PHOTO_PATH,
FIELD_EXECUTIVE_SIGNATURE_PATH,FIELD_EXECUTIVE_SPOUSE_NAME,
FIELD_EXECUTIVE_SPOUSE_RELATION,FIELD_EXECUTIVE_SPOUSE_AGE,
FIELD_EXECUTIVE_SPOUSE_DOB,FIELD_EXECUTIVE_NO_OF_CHILDREN,
FIELD_EXECUTIVE_NO_OF_MALE_CHILDREN,FIELD_EXECUTIVE_NO_OF_FEMALE_CHILDREN,
FIELD_EXECUTIVE_FATHERS_NAME,FIELD_EXECUTIVE_FATHERS_AGE,
FIELD_EXECUTIVE_FATHERS_DOB,FIELD_EXECUTIVE_FATHERS_STATUS,
FIELD_EXECUTIVE_MOTHERS_NAME,FIELD_EXECUTIVE_MOTHERS_AGE,
FIELD_EXECUTIVE_MOTHERS_DOB,FIELD_EXECUTIVE_MOTHERS_STATUS,
INTRO_FIELD_EXECUTIVE_ID,DELETESTATUS,FIELD_EXECUTIVE_ID,
USERID,CREATEDDATE,MODIFIEDDATE,PRINTED_ID,Activate
)
VALUES
(@FIELD_EXECUTIVE_ENROLLMENT_NO,@BRANCH_ID,@AGENT_DESIGN_ID,
@DATE_OF_ENROLLEMENT,@FIELD_EXECUTIVE_NAME,@FIELD_EXECUTIVE_AGE,
@FIELD_EXECUTIVE_DOB,@FIELD_EXECUTIVE_RELATION,@FIELD_EXECUTIVE_RELATION_NAME,
@FIELD_EXECUTIVE_ADDRESS,@FIELD_EXECUTIVE_DISTRICT,
@FIELD_EXECUTIVE_STATE_ID,@FIELD_EXECUTIVE_PIN,
@FIELD_EXECUTIVE_PHONE_NO,@FIELD_EXECUTIVE_CELL_NO,
@FIELD_EXECUTIVE_PAN_NO,@FIELD_EXECUTIVE_PASSPORT_NO,
@FIELD_EXECUTIVE_BANK_AC_NO,@FIELD_EXECUTIVE_BANK_NAME,
@FIELD_EXECUTIVE_BANK_PLACE,@FIELD_EXECUTIVE_PHOTO_PATH,
@FIELD_EXECUTIVE_SIGNATURE_PATH,@FIELD_EXECUTIVE_SPOUSE_NAME,
@FIELD_EXECUTIVE_SPOUSE_RELATION,@FIELD_EXECUTIVE_SPOUSE_AGE,
@FIELD_EXECUTIVE_SPOUSE_DOB,@FIELD_EXECUTIVE_NO_OF_CHILDREN,
@FIELD_EXECUTIVE_NO_OF_MALE_CHILDREN,@FIELD_EXECUTIVE_NO_OF_FEMALE_CHILDREN,
@FIELD_EXECUTIVE_FATHERS_NAME,@FIELD_EXECUTIVE_FATHERS_AGE,
@FIELD_EXECUTIVE_FATHERS_DOB,@FIELD_EXECUTIVE_FATHERS_STATUS,
@FIELD_EXECUTIVE_MOTHERS_NAME,@FIELD_EXECUTIVE_MOTHERS_AGE,
@FIELD_EXECUTIVE_MOTHERS_DOB,@FIELD_EXECUTIVE_MOTHERS_STATUS,
@INTRO_FIELD_EXECUTIVE_ID,@DELETESTATUS,@FIELD_EXECUTIVE_ID,
@userid,@CREATEDDATE,@MODIFIEDDATE,@PRINTED_ID,@Activate
)
END
-----------------------------------------------------
After Update statement trigger:
CREATE TRIGGER Field_Executive_Trigger_Update
ON FIELD_EXECUTIVE_DETAILS
FOR UPDATE
AS
BEGIN
DECLARE @FIELD_EXECUTIVE_ENROLLMENT_NO INT
SET @FIELD_EXECUTIVE_ENROLLMENT_NO = (SELECT FIELD_EXECUTIVE_ENROLLMENT_NO FROM inserted)
DECLARE @BRANCH_ID INT
SET @BRANCH_ID = (SELECT BRANCH_ID FROM inserted)
DECLARE @AGENT_DESIGN_ID INT
SET @AGENT_DESIGN_ID = (SELECT AGENT_DESIGN_ID FROM inserted)
DECLARE @DATE_OF_ENROLLEMENT DATETIME
SET @DATE_OF_ENROLLEMENT = (SELECT DATE_OF_ENROLLEMENT FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_NAME VARCHAR(50)
SET @FIELD_EXECUTIVE_NAME = (SELECT FIELD_EXECUTIVE_NAME FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_AGE FLOAT
SET @FIELD_EXECUTIVE_AGE = (SELECT FIELD_EXECUTIVE_AGE FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_DOB DATETIME
SET @FIELD_EXECUTIVE_DOB = (SELECT FIELD_EXECUTIVE_DOB FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_RELATION CHAR(10)
SET @FIELD_EXECUTIVE_RELATION = (SELECT FIELD_EXECUTIVE_RELATION FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_RELATION_NAME VARCHAR(50)
SET @FIELD_EXECUTIVE_RELATION_NAME = (SELECT FIELD_EXECUTIVE_RELATION_NAME FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_ADDRESS VARCHAR(500)
SET @FIELD_EXECUTIVE_ADDRESS = (SELECT FIELD_EXECUTIVE_ADDRESS FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_DISTRICT VARCHAR(50)
SET @FIELD_EXECUTIVE_DISTRICT = (SELECT FIELD_EXECUTIVE_DISTRICT FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_STATE_ID INT
SET @FIELD_EXECUTIVE_STATE_ID = (SELECT FIELD_EXECUTIVE_STATE_ID FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_PIN FLOAT
SET @FIELD_EXECUTIVE_PIN = (SELECT FIELD_EXECUTIVE_PIN FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_PHONE_NO VARCHAR(50)
SET @FIELD_EXECUTIVE_PHONE_NO = (SELECT FIELD_EXECUTIVE_PHONE_NO FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_CELL_NO VARCHAR(50)
SET @FIELD_EXECUTIVE_CELL_NO = (SELECT FIELD_EXECUTIVE_CELL_NO FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_PAN_NO VARCHAR(50)
SET @FIELD_EXECUTIVE_PAN_NO = (SELECT FIELD_EXECUTIVE_PAN_NO FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_PASSPORT_NO VARCHAR(50)
SET @FIELD_EXECUTIVE_PASSPORT_NO = (SELECT FIELD_EXECUTIVE_PASSPORT_NO FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_BANK_AC_NO VARCHAR(50)
SET @FIELD_EXECUTIVE_BANK_AC_NO = (SELECT FIELD_EXECUTIVE_BANK_AC_NO FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_BANK_NAME VARCHAR(50)
SET @FIELD_EXECUTIVE_BANK_NAME = (SELECT FIELD_EXECUTIVE_BANK_NAME FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_BANK_PLACE VARCHAR(50)
SET @FIELD_EXECUTIVE_BANK_PLACE = (SELECT FIELD_EXECUTIVE_BANK_PLACE FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_PHOTO_PATH VARCHAR(500)
SET @FIELD_EXECUTIVE_PHOTO_PATH = (SELECT FIELD_EXECUTIVE_PHOTO_PATH FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_SIGNATURE_PATH VARCHAR(500)
SET @FIELD_EXECUTIVE_SIGNATURE_PATH = (SELECT FIELD_EXECUTIVE_SIGNATURE_PATH FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_SPOUSE_NAME VARCHAR(50)
SET @FIELD_EXECUTIVE_SPOUSE_NAME = (SELECT FIELD_EXECUTIVE_SPOUSE_NAME FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_SPOUSE_RELATION VARCHAR(50)
SET @FIELD_EXECUTIVE_SPOUSE_NAME = (SELECT FIELD_EXECUTIVE_SPOUSE_NAME FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_SPOUSE_AGE FLOAT
SET @FIELD_EXECUTIVE_SPOUSE_AGE = (SELECT FIELD_EXECUTIVE_SPOUSE_AGE FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_SPOUSE_DOB DATETIME
SET @FIELD_EXECUTIVE_SPOUSE_DOB = (SELECT FIELD_EXECUTIVE_SPOUSE_DOB FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_NO_OF_CHILDREN INT
SET @FIELD_EXECUTIVE_NO_OF_CHILDREN = (SELECT FIELD_EXECUTIVE_NO_OF_CHILDREN FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_NO_OF_MALE_CHILDREN INT
SET @FIELD_EXECUTIVE_NO_OF_MALE_CHILDREN = (SELECT FIELD_EXECUTIVE_NO_OF_MALE_CHILDREN FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_NO_OF_FEMALE_CHILDREN INT
SET @FIELD_EXECUTIVE_NO_OF_FEMALE_CHILDREN = (SELECT FIELD_EXECUTIVE_NO_OF_FEMALE_CHILDREN FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_FATHERS_NAME VARCHAR(50)
SET @FIELD_EXECUTIVE_FATHERS_NAME = (SELECT FIELD_EXECUTIVE_FATHERS_NAME FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_FATHERS_AGE FLOAT
SET @FIELD_EXECUTIVE_FATHERS_AGE = (SELECT FIELD_EXECUTIVE_FATHERS_AGE FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_FATHERS_DOB DATETIME
SET @FIELD_EXECUTIVE_FATHERS_DOB = (SELECT FIELD_EXECUTIVE_FATHERS_DOB FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_FATHERS_STATUS CHAR(1)
SET @FIELD_EXECUTIVE_FATHERS_STATUS = (SELECT FIELD_EXECUTIVE_FATHERS_STATUS FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_MOTHERS_NAME VARCHAR(50)
SET @FIELD_EXECUTIVE_MOTHERS_NAME =(SELECT FIELD_EXECUTIVE_MOTHERS_NAME FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_MOTHERS_AGE FLOAT
SET @FIELD_EXECUTIVE_MOTHERS_AGE = (SELECT FIELD_EXECUTIVE_MOTHERS_AGE FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_MOTHERS_DOB DATETIME
SET @FIELD_EXECUTIVE_MOTHERS_DOB = (SELECT FIELD_EXECUTIVE_MOTHERS_DOB FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_MOTHERS_STATUS CHAR(1)
SET @FIELD_EXECUTIVE_MOTHERS_STATUS = (SELECT FIELD_EXECUTIVE_MOTHERS_STATUS FROM INSERTED)
DECLARE @INTRO_FIELD_EXECUTIVE_ID BIGINT
SET @INTRO_FIELD_EXECUTIVE_ID = (SELECT INTRO_FIELD_EXECUTIVE_ID FROM INSERTED)
DECLARE @DELETESTATUS INT
SET @DELETESTATUS = (SELECT DELETESTATUS FROM INSERTED)
DECLARE @FIELD_EXECUTIVE_ID BIGINT
SET @FIELD_EXECUTIVE_ID = (SELECT FIELD_EXECUTIVE_ID FROM INSERTED)
DECLARE @userid VARCHAR(10)
SET @userid = (SELECT USERID FROM INSERTED)
DECLARE @CREATEDDATE DATETIME
SET @CREATEDDATE = (SELECT CREATEDDATE FROM INSERTED)
DECLARE @MODIFIEDDATE DATETIME
SET @MODIFIEDDATE = (SELECT MODIFIEDDATE FROM INSERTED)
DECLARE @PRINTED_ID INT
SET @PRINTED_ID = (SELECT PRINTED_ID FROM INSERTED)
DECLARE @Activate INT
SET @Activate = (SELECT Activate FROM INSERTED)
UPDATE [SKF_INSURANCE].[dbo].FIELD_EXECUTIVE_DETAILS
SET
FIELD_EXECUTIVE_ENROLLMENT_NO = @FIELD_EXECUTIVE_ENROLLMENT_NO,
BRANCH_ID=@BRANCH_ID,
AGENT_DESIGN_ID=@AGENT_DESIGN_ID,
DATE_OF_ENROLLEMENT=@DATE_OF_ENROLLEMENT,
FIELD_EXECUTIVE_NAME=@FIELD_EXECUTIVE_NAME,
FIELD_EXECUTIVE_AGE = @FIELD_EXECUTIVE_AGE,
FIELD_EXECUTIVE_DOB = @FIELD_EXECUTIVE_DOB,
FIELD_EXECUTIVE_RELATION=@FIELD_EXECUTIVE_RELATION,
FIELD_EXECUTIVE_RELATION_NAME= @FIELD_EXECUTIVE_RELATION_NAME,
FIELD_EXECUTIVE_ADDRESS = @FIELD_EXECUTIVE_ADDRESS,
FIELD_EXECUTIVE_DISTRICT=@FIELD_EXECUTIVE_DISTRICT,
FIELD_EXECUTIVE_STATE_ID = @FIELD_EXECUTIVE_STATE_ID,
FIELD_EXECUTIVE_PIN = @FIELD_EXECUTIVE_PIN,
FIELD_EXECUTIVE_PHONE_NO=@FIELD_EXECUTIVE_PHONE_NO,
FIELD_EXECUTIVE_CELL_NO=@FIELD_EXECUTIVE_CELL_NO,
FIELD_EXECUTIVE_PAN_NO =@FIELD_EXECUTIVE_PAN_NO,
FIELD_EXECUTIVE_PASSPORT_NO=@FIELD_EXECUTIVE_PASSPORT_NO,
FIELD_EXECUTIVE_BANK_AC_NO=@FIELD_EXECUTIVE_BANK_AC_NO,
FIELD_EXECUTIVE_BANK_NAME=@FIELD_EXECUTIVE_BANK_NAME,
FIELD_EXECUTIVE_BANK_PLACE=@FIELD_EXECUTIVE_BANK_PLACE,
FIELD_EXECUTIVE_SIGNATURE_PATH=@FIELD_EXECUTIVE_SIGNATURE_PATH,
FIELD_EXECUTIVE_SPOUSE_NAME=@FIELD_EXECUTIVE_SPOUSE_NAME,
FIELD_EXECUTIVE_SPOUSE_RELATION = @FIELD_EXECUTIVE_SPOUSE_RELATION,
FIELD_EXECUTIVE_SPOUSE_AGE=@FIELD_EXECUTIVE_SPOUSE_AGE,
FIELD_EXECUTIVE_SPOUSE_DOB=@FIELD_EXECUTIVE_SPOUSE_DOB,
FIELD_EXECUTIVE_NO_OF_CHILDREN=@FIELD_EXECUTIVE_NO_OF_CHILDREN,
FIELD_EXECUTIVE_NO_OF_MALE_CHILDREN=@FIELD_EXECUTIVE_NO_OF_MALE_CHILDREN,
FIELD_EXECUTIVE_NO_OF_FEMALE_CHILDREN=@FIELD_EXECUTIVE_NO_OF_FEMALE_CHILDREN,
FIELD_EXECUTIVE_FATHERS_NAME=@FIELD_EXECUTIVE_FATHERS_NAME,
FIELD_EXECUTIVE_FATHERS_AGE=@FIELD_EXECUTIVE_FATHERS_AGE,
FIELD_EXECUTIVE_FATHERS_DOB=@FIELD_EXECUTIVE_FATHERS_DOB,
FIELD_EXECUTIVE_FATHERS_STATUS=@FIELD_EXECUTIVE_FATHERS_STATUS,
FIELD_EXECUTIVE_MOTHERS_NAME=@FIELD_EXECUTIVE_MOTHERS_NAME,
FIELD_EXECUTIVE_MOTHERS_AGE=@FIELD_EXECUTIVE_MOTHERS_AGE,
FIELD_EXECUTIVE_MOTHERS_DOB=@FIELD_EXECUTIVE_MOTHERS_DOB,
FIELD_EXECUTIVE_MOTHERS_STATUS=@FIELD_EXECUTIVE_MOTHERS_STATUS,
INTRO_FIELD_EXECUTIVE_ID=@INTRO_FIELD_EXECUTIVE_ID,
DELETESTATUS=@DELETESTATUS,
USERID=@USERID,CREATEDDATE=@CREATEDDATE,
MODIFIEDDATE=@MODIFIEDDATE,PRINTED_ID=@PRINTED_ID,
Activate=@Activate
WHERE FIELD_EXECUTIVE_ID = @FIELD_EXECUTIVE_ID
end
June 30, 2009 at 11:12 pm
You could have used inserted.column_name syntax as Lowell suggested. That would make cleaner readable code.
July 1, 2009 at 3:46 am
Like Roshan said, the INSERTED virtual table makes both your triggers cleaner and easier to read;
note how your trigger would not work if multiple rows get inserted...it would only move one row, and your data would not be in sync.
finding out later, that the two databases are NOT in sync, because the triggers are not correct should be avoided at all costs.
here's a re-write of your triggers you could try, that would handle multiple inserts or updates correctly:
CREATE TRIGGER TR_INSERTField_Executive_Trigger
ON FIELD_EXECUTIVE_DETAILS
FOR INSERT
AS
BEGIN
INSERT INTO [SKF_INSURANCE].[dbo].FIELD_EXECUTIVE_DETAILS
(FIELD_EXECUTIVE_ENROLLMENT_NO, BRANCH_ID, AGENT_DESIGN_ID,
DATE_OF_ENROLLEMENT, FIELD_EXECUTIVE_NAME, FIELD_EXECUTIVE_AGE,
FIELD_EXECUTIVE_DOB, FIELD_EXECUTIVE_RELATION, FIELD_EXECUTIVE_RELATION_NAME,
FIELD_EXECUTIVE_ADDRESS, FIELD_EXECUTIVE_DISTRICT,
FIELD_EXECUTIVE_STATE_ID, FIELD_EXECUTIVE_PIN,
FIELD_EXECUTIVE_PHONE_NO, FIELD_EXECUTIVE_CELL_NO,
FIELD_EXECUTIVE_PAN_NO, FIELD_EXECUTIVE_PASSPORT_NO,
FIELD_EXECUTIVE_BANK_AC_NO, FIELD_EXECUTIVE_BANK_NAME,
FIELD_EXECUTIVE_BANK_PLACE, FIELD_EXECUTIVE_PHOTO_PATH,
FIELD_EXECUTIVE_SIGNATURE_PATH, FIELD_EXECUTIVE_SPOUSE_NAME,
FIELD_EXECUTIVE_SPOUSE_RELATION, FIELD_EXECUTIVE_SPOUSE_AGE,
FIELD_EXECUTIVE_SPOUSE_DOB, FIELD_EXECUTIVE_NO_OF_CHILDREN,
FIELD_EXECUTIVE_NO_OF_MALE_CHILDREN, FIELD_EXECUTIVE_NO_OF_FEMALE_CHILDREN,
FIELD_EXECUTIVE_FATHERS_NAME, FIELD_EXECUTIVE_FATHERS_AGE,
FIELD_EXECUTIVE_FATHERS_DOB, FIELD_EXECUTIVE_FATHERS_STATUS,
FIELD_EXECUTIVE_MOTHERS_NAME, FIELD_EXECUTIVE_MOTHERS_AGE,
FIELD_EXECUTIVE_MOTHERS_DOB, FIELD_EXECUTIVE_MOTHERS_STATUS,
INTRO_FIELD_EXECUTIVE_ID, DELETESTATUS, FIELD_EXECUTIVE_ID,
USERID, CREATEDDATE, MODIFIEDDATE, PRINTED_ID, Activate
)
SELECT
FIELD_EXECUTIVE_ENROLLMENT_NO, BRANCH_ID, AGENT_DESIGN_ID,
DATE_OF_ENROLLEMENT, FIELD_EXECUTIVE_NAME, FIELD_EXECUTIVE_AGE,
FIELD_EXECUTIVE_DOB, FIELD_EXECUTIVE_RELATION, FIELD_EXECUTIVE_RELATION_NAME,
FIELD_EXECUTIVE_ADDRESS, FIELD_EXECUTIVE_DISTRICT,
FIELD_EXECUTIVE_STATE_ID, FIELD_EXECUTIVE_PIN,
FIELD_EXECUTIVE_PHONE_NO, FIELD_EXECUTIVE_CELL_NO,
FIELD_EXECUTIVE_PAN_NO, FIELD_EXECUTIVE_PASSPORT_NO,
FIELD_EXECUTIVE_BANK_AC_NO, FIELD_EXECUTIVE_BANK_NAME,
FIELD_EXECUTIVE_BANK_PLACE, FIELD_EXECUTIVE_PHOTO_PATH,
FIELD_EXECUTIVE_SIGNATURE_PATH, FIELD_EXECUTIVE_SPOUSE_NAME,
FIELD_EXECUTIVE_SPOUSE_RELATION, FIELD_EXECUTIVE_SPOUSE_AGE,
FIELD_EXECUTIVE_SPOUSE_DOB, FIELD_EXECUTIVE_NO_OF_CHILDREN,
FIELD_EXECUTIVE_NO_OF_MALE_CHILDREN, FIELD_EXECUTIVE_NO_OF_FEMALE_CHILDREN,
FIELD_EXECUTIVE_FATHERS_NAME, FIELD_EXECUTIVE_FATHERS_AGE,
FIELD_EXECUTIVE_FATHERS_DOB, FIELD_EXECUTIVE_FATHERS_STATUS,
FIELD_EXECUTIVE_MOTHERS_NAME, FIELD_EXECUTIVE_MOTHERS_AGE,
FIELD_EXECUTIVE_MOTHERS_DOB, FIELD_EXECUTIVE_MOTHERS_STATUS,
INTRO_FIELD_EXECUTIVE_ID, DELETESTATUS, FIELD_EXECUTIVE_ID,
USERID, CREATEDDATE, MODIFIEDDATE, PRINTED_ID, Activate
FROM INSERTED
END
-----------------------------------------------------
GO
--After Update statement trigger:
CREATE TRIGGER TR_UPDATEField_Executive_Trigger
ON FIELD_EXECUTIVE_DETAILS
AFTER UPDATE
AS
BEGIN
UPDATE [SKF_INSURANCE].[dbo].FIELD_EXECUTIVE_DETAILS
SET
FIELD_EXECUTIVE_ENROLLMENT_NO = INSERTED.FIELD_EXECUTIVE_ENROLLMENT_NO,
BRANCH_ID = INSERTED.BRANCH_ID,
AGENT_DESIGN_ID = INSERTED.AGENT_DESIGN_ID,
DATE_OF_ENROLLEMENT = INSERTED.DATE_OF_ENROLLEMENT,
FIELD_EXECUTIVE_NAME = INSERTED.FIELD_EXECUTIVE_NAME,
FIELD_EXECUTIVE_AGE = INSERTED.FIELD_EXECUTIVE_AGE,
FIELD_EXECUTIVE_DOB = INSERTED.FIELD_EXECUTIVE_DOB,
FIELD_EXECUTIVE_RELATION = INSERTED.FIELD_EXECUTIVE_RELATION,
FIELD_EXECUTIVE_RELATION_NAME = INSERTED.FIELD_EXECUTIVE_RELATION_NAME,
FIELD_EXECUTIVE_ADDRESS = INSERTED.FIELD_EXECUTIVE_ADDRESS,
FIELD_EXECUTIVE_DISTRICT = INSERTED.FIELD_EXECUTIVE_DISTRICT,
FIELD_EXECUTIVE_STATE_ID = INSERTED.FIELD_EXECUTIVE_STATE_ID,
FIELD_EXECUTIVE_PIN = INSERTED.FIELD_EXECUTIVE_PIN,
FIELD_EXECUTIVE_PHONE_NO = INSERTED.FIELD_EXECUTIVE_PHONE_NO,
FIELD_EXECUTIVE_CELL_NO = INSERTED.FIELD_EXECUTIVE_CELL_NO,
FIELD_EXECUTIVE_PAN_NO = INSERTED.FIELD_EXECUTIVE_PAN_NO,
FIELD_EXECUTIVE_PASSPORT_NO = INSERTED.FIELD_EXECUTIVE_PASSPORT_NO,
FIELD_EXECUTIVE_BANK_AC_NO = INSERTED.FIELD_EXECUTIVE_BANK_AC_NO,
FIELD_EXECUTIVE_BANK_NAME = INSERTED.FIELD_EXECUTIVE_BANK_NAME,
FIELD_EXECUTIVE_BANK_PLACE = INSERTED.FIELD_EXECUTIVE_BANK_PLACE,
FIELD_EXECUTIVE_SIGNATURE_PATH = INSERTED.FIELD_EXECUTIVE_SIGNATURE_PATH,
FIELD_EXECUTIVE_SPOUSE_NAME = INSERTED.FIELD_EXECUTIVE_SPOUSE_NAME,
FIELD_EXECUTIVE_SPOUSE_RELATION = INSERTED.FIELD_EXECUTIVE_SPOUSE_RELATION,
FIELD_EXECUTIVE_SPOUSE_AGE = INSERTED.FIELD_EXECUTIVE_SPOUSE_AGE,
FIELD_EXECUTIVE_SPOUSE_DOB = INSERTED.FIELD_EXECUTIVE_SPOUSE_DOB,
FIELD_EXECUTIVE_NO_OF_CHILDREN = INSERTED.FIELD_EXECUTIVE_NO_OF_CHILDREN,
FIELD_EXECUTIVE_NO_OF_MALE_CHILDREN = INSERTED.FIELD_EXECUTIVE_NO_OF_MALE_CHILDREN,
FIELD_EXECUTIVE_NO_OF_FEMALE_CHILDREN = INSERTED.FIELD_EXECUTIVE_NO_OF_FEMALE_CHILDREN,
FIELD_EXECUTIVE_FATHERS_NAME = INSERTED.FIELD_EXECUTIVE_FATHERS_NAME,
FIELD_EXECUTIVE_FATHERS_AGE = INSERTED.FIELD_EXECUTIVE_FATHERS_AGE,
FIELD_EXECUTIVE_FATHERS_DOB = INSERTED.FIELD_EXECUTIVE_FATHERS_DOB,
FIELD_EXECUTIVE_FATHERS_STATUS = INSERTED.FIELD_EXECUTIVE_FATHERS_STATUS,
FIELD_EXECUTIVE_MOTHERS_NAME = INSERTED.FIELD_EXECUTIVE_MOTHERS_NAME,
FIELD_EXECUTIVE_MOTHERS_AGE = INSERTED.FIELD_EXECUTIVE_MOTHERS_AGE,
FIELD_EXECUTIVE_MOTHERS_DOB = INSERTED.FIELD_EXECUTIVE_MOTHERS_DOB,
FIELD_EXECUTIVE_MOTHERS_STATUS = INSERTED.FIELD_EXECUTIVE_MOTHERS_STATUS,
INTRO_FIELD_EXECUTIVE_ID = INSERTED.INTRO_FIELD_EXECUTIVE_ID,
DELETESTATUS = INSERTED.DELETESTATUS,
USERID = INSERTED.USERID,
CREATEDDATE = INSERTED.CREATEDDATE,
MODIFIEDDATE = INSERTED.MODIFIEDDATE,
PRINTED_ID = INSERTED.PRINTED_ID,
Activate = INSERTED.Activate
FROM INSERTED
WHERE FIELD_EXECUTIVE_ID = INSERTED.FIELD_EXECUTIVE_ID
end
Lowell
July 1, 2009 at 9:50 am
Please do as Lowell has suggested in his last post. You definitely want you triggers to handle multi-row changes.
If you are going to another server I would recommend looking at a different solution other than a trigger.
See this article for advice[/url] on triggers.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply