用触发器来自动实时备份重要表数据--downmoon

在数据库有一个比较重要的全局表，为了防止误操作，使用触发器自动生成实时备份数据

SQL语句如下：

-- 原表
if exists ( select * from dbo.sysobjects where id = object_id (N ' [Veg_Enum] ' ) and OBJECTPROPERTY (id,N ' IsUserTable ' ) = 1 )
drop table [ Veg_Enum ]
GO

CREATE TABLE [ Veg_Enum ] (
[ E_ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ E_Name ] [ nvarchar ] ( 50 )COLLATEChinese_PRC_CI_AS NOT NULL CONSTRAINT [ DF__Veg_Enum__E_Name__6497E884 ] DEFAULT ( ' 枚举名称 ' ),
[ E_Type ] [ int ] NOT NULL CONSTRAINT [ DF__Veg_Enum__E_Type__658C0CBD ] DEFAULT ( 0 ),
[ E_TypeName ] [ nvarchar ] ( 50 )COLLATEChinese_PRC_CI_AS NOT NULL CONSTRAINT [ DF__Veg_Enum__E_Type__668030F6 ] DEFAULT ( ' 枚举类型名称 ' ),
[ E_Value ] [ int ] NOT NULL CONSTRAINT [ DF__Veg_Enum__E_Valu__6774552F ] DEFAULT ( 0 ),
[ E_Parent ] [ int ] NOT NULL CONSTRAINT [ DF__Veg_Enum__E_Pare__68687968 ] DEFAULT ( 0 ),
[ E_TypeParent ] [ int ] NOT NULL CONSTRAINT [ DF__Veg_Enum__E_Type__695C9DA1 ] DEFAULT ( 0 ),
[ O_ID ] [ int ] NOT NULL CONSTRAINT [ DF_Veg_Enum_O_ID ] DEFAULT ( 0 ),
[ O_Name ] [ nvarchar ] ( 20 )COLLATEChinese_PRC_CI_AS NULL ,
[ O_Code ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ IsActive ] [ tinyint ] NOT NULL CONSTRAINT [ DF__Veg_Enum__IsActi__6A50C1DA ] DEFAULT ( 0 ),
[ F1 ] [ int ] NOT NULL CONSTRAINT [ DF__Veg_Enum__F1__6B44E613 ] DEFAULT ( 0 ),
[ F2 ] [ int ] NOT NULL CONSTRAINT [ DF__Veg_Enum__F2__6C390A4C ] DEFAULT ( 0 ),
[ F3 ] [ int ] NULL ,
[ F4 ] [ int ] NULL ,
[ F5 ] [ int ] NULL ,
[ F6 ] [ int ] NULL ,
[ F7 ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ F8 ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ F9 ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ F10 ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ F11 ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ F12 ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ CreateTime ] [ datetime ] NOT NULL CONSTRAINT [ DF__veg_Enum__Create__6D2D2E85 ] DEFAULT ( getdate ()),
[ B_date ] [ datetime ] NULL ,
[ E_Date ] [ datetime ] NULL ,
CONSTRAINT [ PK_VEG_ENUM ] PRIMARY KEY CLUSTERED
(
[ E_ID ]
) ON [ PRIMARY ]
) ON [ PRIMARY ]
GO

-- 备份表，注意多了个PKID字段
if exists ( select * from dbo.sysobjects where id = object_id (N ' [Veg_Enum_BAk] ' ) and OBJECTPROPERTY (id,N ' IsUserTable ' ) = 1 )
drop table [ Veg_Enum_BAk ]
GO

CREATE TABLE [ Veg_Enum_BAk ] (
[ PKID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ E_ID ] [ int ] NOT NULL ,
[ E_Name ] [ nvarchar ] ( 50 )COLLATEChinese_PRC_CI_AS NOT NULL CONSTRAINT [ DF__Veg_Enum__E_Name__6497E884000 ] DEFAULT ( ' 枚举名称 ' ),
[ E_Type ] [ int ] NOT NULL CONSTRAINT [ DF__Veg_Enum__E_Type__658C0CBD000 ] DEFAULT ( 0 ),
[ E_TypeName ] [ nvarchar ] ( 50 )COLLATEChinese_PRC_CI_AS NOT NULL CONSTRAINT [ DF__Veg_Enum__E_Type__668030F6000 ] DEFAULT ( ' 枚举类型名称 ' ),
[ E_Value ] [ int ] NOT NULL CONSTRAINT [ DF__Veg_Enum__E_Valu__6774552F00 ] DEFAULT ( 0 ),
[ E_Parent ] [ int ] NOT NULL CONSTRAINT [ DF__Veg_Enum__E_Pare__68687968000 ] DEFAULT ( 0 ),
[ E_TypeParent ] [ int ] NOT NULL CONSTRAINT [ DF__Veg_Enum__E_Type__695C9DA1000 ] DEFAULT ( 0 ),
[ O_ID ] [ int ] NOT NULL CONSTRAINT [ DF_Veg_Enum_O_ID00 ] DEFAULT ( 0 ),
[ O_Name ] [ nvarchar ] ( 20 )COLLATEChinese_PRC_CI_AS NULL ,
[ O_Code ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ IsActive ] [ tinyint ] NOT NULL CONSTRAINT [ DF__Veg_Enum__IsActi__6A50C1DA00 ] DEFAULT ( 0 ),
[ F1 ] [ int ] NOT NULL CONSTRAINT [ DF__Veg_Enum__F1__6B44E61300 ] DEFAULT ( 0 ),
[ F2 ] [ int ] NOT NULL CONSTRAINT [ DF__Veg_Enum__F2__6C390A4C00 ] DEFAULT ( 0 ),
[ F3 ] [ int ] NULL ,
[ F4 ] [ int ] NULL ,
[ F5 ] [ int ] NULL ,
[ F6 ] [ int ] NULL ,
[ F7 ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ F8 ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ F9 ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ F10 ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ F11 ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ F12 ] [ nvarchar ] ( 255 )COLLATEChinese_PRC_CI_AS NULL ,
[ CreateTime ] [ datetime ] NOT NULL CONSTRAINT [ DF__veg_Enum__Create__6D2D2E85000 ] DEFAULT ( getdate ()),
[ B_date ] [ datetime ] NULL ,
[ E_Date ] [ datetime ] NULL ,
CONSTRAINT [ PK_Veg_Enum_BAk ] PRIMARY KEY CLUSTERED
(
[ PKID ]
) ON [ PRIMARY ]
) ON [ PRIMARY ]
GO

/**/ /* *****对象:触发器dbo.Trigger_SynTradeTypeForInsertOrUpdate脚本日期:2007-3-421:33:23***** */




ALTER TRIGGER Trigger_SynVeg_EnumTermForInsertOrUpdate
ON veg_Enum
FOR Insert , Update
AS

BEGIN TRAN
-- updateveg_EnumsetO_Name=(cast(E_TypeParentasnvarchar(10))+'年'+cast(E_Valueasnvarchar(10))+'月')
-- whereE_Type=2001andE_IDin(selectE_IDfrominserted)


insert into dbo.Veg_Enum_BAk
(
E_ID,E_Name,E_Type,E_TypeName,
E_Value,E_Parent,E_TypeParent,O_ID,
O_Name,O_Code,IsActive,F1,
F2,F3,F4,F5,
F6,F7,F8,F9,
F10,F11,F12,CreateTime,
B_date,E_Date
)
select E_ID,E_Name,E_Type,E_TypeName,
E_Value,E_Parent,E_TypeParent,O_ID,
O_Name,O_Code,IsActive,F1,
F2,F3,F4,F5,
F6,F7,F8,F9,
F10,F11,F12,CreateTime,
B_date,E_Date
from Veg_Enum where E_ID in ( select E_ID from inserted)

IF ( @@ERROR <> 0 )
Begin
ROLLBACK TRAN
RAISERROR ( ' 同步数据时出错! ' , 16 , 1 )
Return
End
COMMIT TRAN







GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO