CREATE TABLE [dbo].[T_Guest_Info](
[Mustid] [int] IDENTITY(1,1) NOT NULL,
[TotalMoney] [decimal](18, 2) NULL CONSTRAINT [DF_T_Guest_Info_TotalMoney] DEFAULT ((0)),
[Integral] [decimal](18, 2) NULL CONSTRAINT [DF_T_Guest_Info_Integral] DEFAULT ((0)),
[ChangeSo] [nvarchar](150) NULL,
[GID] [nvarchar](50) NOT NULL,
[Mobile] [nvarchar](50) NULL,
[AddressTel] [nvarchar](50) NULL,
[StandbyTel] [nvarchar](50) NULL,
[Name] [nvarchar](50) NULL,
[age] [nvarchar](50) NULL,
[Sex] [varchar](50) NULL,
[Province] [nvarchar](50) NULL,
[City] [nvarchar](50) NULL,
[Area] [nvarchar](50) NULL,
[Address] [nvarchar](50) NULL,
[ArrivedCode] [varchar](50) NULL,
[Postalcode] [varchar](50) NULL,
[Remarks] [nvarchar](50) NULL,
[IsSell] [varchar](50) NULL CONSTRAINT [DF_T_Guest_Info_IsSell] DEFAULT ('no'),
[GType] [nvarchar](50) NULL CONSTRAINT [DF_T_Guest_Info_GType] DEFAULT ((1)),
[GStatus] [nvarchar](50) NULL,
[GStage] [nvarchar](50) NULL,
[GKind] [nvarchar](50) NULL,
[RegTime] [datetime] NULL CONSTRAINT [DF_T_Guest_Info_RegTime] DEFAULT (getdate()),
[IsDel] [varchar](50) NULL CONSTRAINT [DF_T_Guest_Info_IsDel] DEFAULT ('no'),
[PerfectTime] [datetime] NULL,
[Operate] [nvarchar](50) NULL,
[AssignHits] [int] NULL CONSTRAINT [DF_T_Guest_Info_AssignHits] DEFAULT ((0)),
[ChangePeople] [nvarchar](50) NULL,
[IsAssign] [varchar](50) NULL CONSTRAINT [DF_T_Guest_Info_IsAssign] DEFAULT ((0)),
[MCGroup] [nvarchar](50) NULL
) ON [PRIMARY]
GO
我的触发器脚本：
create TRIGGER [dbo].[T_GuestIntegralChange_Log]
ON [dbo].[T_Guest_Info]
FOR UPDATE
AS
--IF COLUMNS_UPDATED() & 10 <10 AND COLUMNS_UPDATED() & 10>0
IF (SUBSTRING(COLUMNS_UPDATED(),1,1)&14)>0--/*记录表的2，3，4列的数据变更*/
BEGIN
/*保存旧的信息*/
INSERT INTO [HXRTDB].[dbo].[T_GuestIntegral_Log]
([GID],
[TotalMoneyOld]
,[TotalIntegralOld]
,[ChangeSoOld]
,[ChangePeopleOld])
SELECT
[GID],
[TotalMoney]
,[Integral]
,[ChangeSo]
,[ChangePeople]
FROM deleted
/*保存新的信息*/
UPDATE [HXRTDB].[dbo].[T_GuestIntegral_Log]
SET
[GID] = (SELECT GID FROM inserted),
--,[TotalMoneyOld] = <TotalMoneyOld, decimal(18,2),>
[TotalMoneyNew] = (SELECT [TotalMoney] FROM inserted)
--,[TotalIntegralOld] = <TotalIntegralOld, decimal(18,2),>
,[TotalIntegralNew] = (SELECT [Integral] FROM inserted)
,[ChangeSoNew] = (SELECT [ChangeSo] FROM inserted)
,[ChangePeopleNew] = (SELECT [ChangePeople] FROM inserted)
WHERE
MUSTID =(SELECT TOP 1 MUSTID FROM T_GuestIntegral_Log ORDER BY MUSTID DESC)
END
update t_guest_info set totalmoney='1111' where mustid ='151'
SELECT *
FROM T_GuestIntegral_Log
还有触发器影响的表的脚本 在下面接着贴出来：
CREATE TABLE [dbo].[T_GuestIntegral_Log](
[Mustid] [int] IDENTITY(1,1) NOT NULL,
[GID] [nvarchar](50) NULL,
[TotalMoneyOld] [decimal](18, 2) NULL,
[TotalMoneyNew] [decimal](18, 2) NULL,
[TotalIntegralOld] [decimal](18, 2) NULL,
[TotalIntegralNew] [decimal](18, 2) NULL,
[ChangeSoOld] [nvarchar](150) NULL,
[ChangeSoNew] [nvarchar](150) NULL,
[ChangePeopleOld] [nvarchar](50) NULL,
[ChangePeopleNew] [nvarchar](50) NULL,
CONSTRAINT [PK_T_GuestIntegra_Log] PRIMARY KEY CLUSTERED
(
[Mustid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
