GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

Create trigger [dbo].[tri_device]

on [dbo].[Device]

for insert,update,delete

as

begin

if EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)

insert into RealData(DtuId,Val,RcvTime)

select DtuId,'0',getdate() from inserted;

IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)

update RealData set DtuId=a.DtuId from inserted a

IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)

delete from RealData where DtuId=(select a.DtuId from deleted a)

end;