在数据库有一个比较重要的全局表,为了防止误操作,使用触发器自动生成实时备份数据
SQL语句如下:
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
--原表
if exists (select * from dbo.sysobjects where id = object_id(N'[Veg_Enum]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [Veg_Enum]
GO
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
CREATE TABLE [Veg_Enum] (
[E_ID] [int] IDENTITY (1, 1) NOT NULL ,
[E_Name] [nvarchar] (50) COLLATE Chinese_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) COLLATE Chinese_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) COLLATE Chinese_PRC_CI_AS NULL ,
[O_Code] [nvarchar] (255) COLLATE Chinese_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) COLLATE Chinese_PRC_CI_AS NULL ,
[F8] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[F9] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[F10] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[F11] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[F12] [nvarchar] (255) COLLATE Chinese_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
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
--备份表,注意多了个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
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
CREATE TABLE [Veg_Enum_BAk] (
[PKID] [int] IDENTITY (1, 1) NOT NULL ,
[E_ID] [int] NOT NULL ,
[E_Name] [nvarchar] (50) COLLATE Chinese_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) COLLATE Chinese_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) COLLATE Chinese_PRC_CI_AS NULL ,
[O_Code] [nvarchar] (255) COLLATE Chinese_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) COLLATE Chinese_PRC_CI_AS NULL ,
[F8] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[F9] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[F10] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[F11] [nvarchar] (255) COLLATE Chinese_PRC_CI_AS NULL ,
[F12] [nvarchar] (255) COLLATE Chinese_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
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
/****** 对象: 触发器 dbo.Trigger_SynTradeTypeForInsertOrUpdate 脚本日期: 2007-3-4 21:33:23 ******/
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
ALTER TRIGGER Trigger_SynVeg_EnumTermForInsertOrUpdate
ON veg_Enum
FOR Insert,Update
AS
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
BEGIN TRAN
--update veg_Enum set O_Name=(cast(E_TypeParent as nvarchar(10))+'年'+cast(E_Value as nvarchar(10))+'月')
-- where E_Type=2001 and E_ID in (select E_ID from inserted)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
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
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
SQL语句如下:
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/ExpandedBlockStart.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)
![](http://images.csdn.net/syntaxhighlighting/OutliningIndicators/None.gif)