November 15, 2011 at 9:35 pm
Dear All,
For the purpose of duplication (this is intentional)i have created a duplicate table inside database called Employee and Employee_rep.
Employee is the base table whatever the transaction happened on it,should be applied for Employee_rep as well, in terms of INSERT/UPDATE/DELETE.
I have tried to create triggers for this purpose for insert
CREATE TRIGGER trgAfterInsert_employee ON [dbo].employee
FOR INSERT
AS
insert into EMployee_rep(
NameGiven,
NameFamily,
WID,
WindowsUserName,
Email,
Country_ID from inserted
Go
i need similar trigger for DELETE and update... whenevr the record has been deleted from base table in the duplicate table also it should delete and its same for update.
Could you pls help.
November 16, 2011 at 3:18 am
Let me say that your table design is a bit questionable. For instance, what's your primary key?
I assumed it was WindowsUserName.
This should put you in the right direction:
CREATE TRIGGER trgAfterInsert_employee ON [dbo].employee
FOR INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO EMployee_rep(
NameGiven,
NameFamily,
WID,
WindowsUserName,
Email,
Country_ID
)
SELECT NameGiven,
NameFamily,
WID,
WindowsUserName,
Email,
Country_ID
FROM INSERTED
-- Exclude updated records
WHERE WindowsUserName NOT IN (
SELECT WindowsUserName
FROM DELETED
);
UPDATE dest
SET NameGiven = src.NameGiven,
NameFamily = src.NameFamily,
WID = src.WID,
WindowsUserName = src.WindowsUserName,
Email = src.Email,
Country_ID = src.Country_ID
FROM EMployee_rep AS dest
INNER JOIN INSERTED AS src
ON src.WindowsUserName = dest.WindowsUserName;
DELETE dest
FROM DELETED AS src
INNER JOIN EMployee_rep AS dest
ON src.WindowsUserName = dest.WindowsUserName
-- Exclude updated records
WHERE src.WindowsUserName NOT IN (
SELECT WindowsUserName
FROM INSERTED
);
END
GO
-- Gianluca Sartori
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply