关于insert,update的临时表

[bobo928843007]

create trigger GrUpdate
on [dbo].[SC]
after insert,update
as
declare @befGr smallint,@afrGr smallint
if UPDATE(Grade)
begin
select @befGr=DELETED.Grate from DELETED
select @afrGr=INSERTED.Grate from INSERTED
INSERT INTO [JXGL].[dbo].[Gradechange]
([usename]
,[changetime]
,[bfch_value]
,[afch_value])
VALUES
(USER_NAME()
,GETDATE()
,@befGr
,@afrGr)
end
报错：
Msg 207, Level 16, State 1, Procedure GrUpdate, Line 8
Invalid column name 'Grate'.
Msg 207, Level 16, State 1, Procedure GrUpdate, Line 9
Invalid column name 'Grate'.
求大神帮忙

[bobo928843007]

啊，字段搞错了Grade写成Grare

[LongRui888]

好像是把，grade写成了grate了，改成这样试试：

create trigger GrUpdate
on [dbo].[SC]
after insert,update
as
declare @befGr smallint,@afrGr smallint
if UPDATE(Grade)
begin
select @befGr=DELETED.Grade from DELETED
select @afrGr=INSERTED.Grade from INSERTED
INSERT INTO [JXGL].[dbo].[Gradechange]
           ([usename]
           ,[changetime]
           ,[bfch_value]
           ,[afch_value])
     VALUES
           (USER_NAME()
           ,GETDATE()
           ,@befGr
           ,@afrGr)
end

[bobo928843007]

update触发器怎么不是更新一行就触发一次呢，而是把要更新的更新完才触发呢。怎么实现逐行更新逐行触发啊

[LongRui888]

引用 4 楼 bobo928843007 的回复:

update触发器怎么不是更新一行就触发一次呢，而是把要更新的更新完才触发呢。怎么实现逐行更新逐行触发啊

在sql server中的触发器，不是行级触发器，不能实现一行一行的处理，而是比如你要更新100条数据，那么只会触发一次。 只有在oracle中，才支持行级别的触发器。

[dongdongdongJL]

触发器，当然是一条一致行的呀。

[xiaoliaoyun]

批量更新用下面这个试试。

create trigger GrUpdate
on [dbo].[SC]
after insert,update
as
--declare @befGr smallint,@afrGr smallint
if UPDATE(Grade)
begin
--select @befGr=DELETED.Grade from DELETED
--select @afrGr=INSERTED.Grade from INSERTED
INSERT INTO [JXGL].[dbo].[Gradechange]
           ([usename]
           ,[changetime]
           ,[bfch_value]
           ,[afch_value])
SELECT 
            USER_NAME()
           ,GETDATE()
           ,B.Grade
           ,A.Grade
FROM        inserted A
LEFT JOIN   deleted B ON A.usename = B.usename

end

[bobo928843007]

alter trigger GrUpdate on [dbo].[SC] after update as declare @befGr smallint,@aftGr smallint if UPDATE(Grade) begin select DELETED.Grade,Inserted.Grade from Inserted join DELETED on Inserted.Sno=DELETED.Sno and Inserted.Cno=DELETED.Cno DECLARE vendor_cursor CURSOR FOR select DELETED.Grade,Inserted.Grade from Inserted join DELETED on Inserted.Sno=DELETED.Sno and Inserted.Cno=DELETED.Cno OPEN vendor_cursor FETCH NEXT FROM vendor_cursor INTO @befGr, @aftGr WHILE @@FETCH_STATUS = 0 BEGIN INSERT INTO [JXGL].[dbo].[Gradechange] ([usename] ,[changetime] ,[bfch_value] ,[afch_value]) VALUES (USER_NAME() ,GETDATE() ,@befGr ,@aftGr) FETCH NEXT FROM vendor_cursor INTO @befGr, @aftGr END end close vendor_cursor DEALLOCATE vendor_cursor