SQL Server Trigger 使用

 

--Deleted

CREATE TRIGGER [dbo].[Battery_Deleted]
   ON  [dbo].[tblBattery]
   instead of DELETE
AS
set nocount on  --無返回影響行數
insert into dbo.tblBatteryLog select * from deleted;
delete from dbo.tblBattery where batteryid in(select batteryid from deleted);

 

--Updated

CREATE TRIGGER [dbo].[Battery_Updated]
   ON  [dbo].[tblBattery]
   INSTEAD OF UPDATE
AS
declare @tran nvarchar(1);
set @tran='0';
begin try
begin tran @tran;
set nocount on;
insert into dbo.tblBatteryLog select * from deleted;
delete from dbo.tblBattery where batteryid in(select batteryid from deleted);
insert into dbo.tblBattery select * from inserted;
commit tran @tran;
end try
begin catch
rollback tran @tran;
end catch

--Inserted

 

CREATE TRIGGER [dbo].[Battery_Inserted]
   ON  [dbo].[tblBattery]
   INSTEAD OF Insert
AS
set nocount on
insert into dbo.tblBatteryLog select * from inserted;
insert into dbo.tblBattery select * from inserted;