对表A删除或修改时,将表一的部分字段做为一条记录插入表B
问题是哪条是删除或修改的记录
CREATE TRIGGER [fromAToB] ON [dbo].[A]
FOR UPDATE, DELETE
AS
DECLARE
@Phone VARCHAR(11), --手机号
@User VARCHAR(20), --用户名
@Email VARCHAR(30), --邮箱
@State INT, --状态
@InsertDate DATETIME --时间
SET @State="1" --设置状态为1
SET @InsertDate=getDate() ---取得当前时间为历史表的确认时间--取出根据手机号取出删除或修改记录的各值SELECT @Phone=Phone,@User=User,@@Email=Email FROM A where ????????????//此名怎么写????????????--插入历史记录表中
INSERT INTO B (Phone,User,Email,Email,State,InsertDate)
VALUES (@Phone,@User,@Email,@Email,@State,@InsertDate)最好给个例子
问题是哪条是删除或修改的记录
CREATE TRIGGER [fromAToB] ON [dbo].[A]
FOR UPDATE, DELETE
AS
DECLARE
@Phone VARCHAR(11), --手机号
@User VARCHAR(20), --用户名
@Email VARCHAR(30), --邮箱
@State INT, --状态
@InsertDate DATETIME --时间
SET @State="1" --设置状态为1
SET @InsertDate=getDate() ---取得当前时间为历史表的确认时间--取出根据手机号取出删除或修改记录的各值SELECT @Phone=Phone,@User=User,@@Email=Email FROM A where ????????????//此名怎么写????????????--插入历史记录表中
INSERT INTO B (Phone,User,Email,Email,State,InsertDate)
VALUES (@Phone,@User,@Email,@Email,@State,@InsertDate)最好给个例子
select Phone,User,Email From Deleted--lz去看一下deleted和inserted逻辑表
FOR UPDATE, DELETE
AS
--插入历史记录表中
INSERT INTO B (Phone,User,Email,Email,State,InsertDate)
SELECT Phone,User,Email,Email,'1',GETDATE() FROM DELETED
GO
FOR UPDATE, DELETE
AS
INSERT INTO B (Phone,User,Email,State,InsertDate) SELECT Phone,User,Email,'1',GETDATE() FROM DELETED
GO
我原来不知道Delete和UPdate都是从DELETED表中取.