Hi there,

I'm using MS SQL Server 2000 on Server 2003
One table emp and one empArchive

I wanted every time that one record in the emp table will be inserted
updated or deleted.
Same thing will happend in the empArchive table.
So I created three triggers look like this

CREATE TRIGGER myArchiveI
ON dbo.emp
FOR INSERT
AS
SET NOCOUNT ON
insert into dbo.empArchive
select EmpID,FirstName,LastName
from inserted

CREATE TRIGGER myArchiveU
ON dbo.emp
FOR  UPDATE
AS
update dbo.empArchive
SET FirstName = i.FirstName,LastName = i.LastName
From inserted i
Where dbo.empArchive.EmpID = i.EmpID

CREATE TRIGGER  myArchiveD
ON dbo.emp
FOR DELETE
AS
delete dbo.empArchive
from empArchive, deleted
where dbo.empArchive.EmpID = deleted.EmpID

everything works fine so, I decided to combine these triggers into one
trigger.

CREATE TRIGGER myTrig
ON emp
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
--inserted
INSERT INTO empArchive
SELECT EmpID,FirstName,LastName
FROM inserted
--updated
UPDATE empArchive
SET FirstName = i.FirstName, LastName = i.LastName
FROM inserted i
WHERE empArchive.EmpID = i.EmpID
--deleted
delete empArchive
FROM empArchive, deleted
WHERE empArchived.EmpID = deleted.EmpID

Now when I updated the table emp
The record was epdated in emp table
Was deleted from empArchive.

My question is How to combine these three trigger into one that will work
fine ?
( I know that the insert is like delete the system cache table )

Thanks an advance,

Ed Dror

Not sure why you're better off that way.  Combining them will require that
you check what kind of action just happened, whereas the system will do that
check for you if you have separate FOR action triggers.

IF @@ROWCOUNT = 0
    RETURN;

IF EXISTS (SELECT 1 FROM inserted) AND EXISTS (SELECT 1 FROM deleted)
BEGIN
    -- update action
END
ELSE
BEGIN
    IF EXISTS (SELECT 1 FROM inserted)
    BEGIN
        -- insert action
    END
    IF EXISTS (SELECT 1 FROM deleted)
    BEGIN
        -- delete action
    END
END

COM+ Applications hanging
Who Added Indexing to Table?
sql weird
NOLOCK
Retrieval of IDENTITY after "bulk" insert?