alter TRIGGER [tSynUserInfoUpdate] ON dbo.PersonInfo
FOR UPDATE
AS
begin
declare @id int
set @id = @@identity --更新记录的时候,获取不到???
print @id
declare @uname varchar(20),@nickname varchar(20),@pwd char(32),@gender bit,@regip varchar(15),@regdate smalldatetime,@lastip varchar(15),
@lastlogintime datetime,@account decimal,@email varchar(50),@bday char(10),
@qq varchar(12),@msn varchar(40),@realname nvarchar(10),@idcard varchar(20),@mobile varchar(20),@tel varchar(20),@homepage varchar(80)
--获取记录
select @uname=PI_UserName,@nickname=PI_NickName,@pwd=PI_password,@gender=PI_gender,@regip=PI_RegisterIP,@regdate=PI_registerTime,@lastip=PI_lastLoginIp,
@lastlogintime=PI_LastLoginTime,@account=PI_account,@email=PI_email,@bday=PI_Birthday --主表数据
,@qq=PI_QQ,@msn=PI_msn,@realname=PI_realname,@idcard=PI_idcard,@mobile=PI_mobile,@tel=PI_tel,@homepage=PI_homepage --扩展表数据
from dbo.PersonInfo where id=@id
begin tran
--同步主表
update dnt_users set username=@uname,nickname=@nickname,[password]=@pwd,gender=@gender,regip=@regip,joindate=@regdate,lastip=@lastip,lastvisit=@lastlogintime,credits=@account,email=@email,bday=@bday where PI_ID=@id
--同步扩展表
update dnt_userfields set qq=@qq,msn=@msn,realname=@realname,idcard=@idcard,mobile=@mobile,phone=@tel,website=@homepage where PI_ID=@id
commit tran
end
GOset @id = @@identity --更新记录的时候,获取不到???
在insert的触发器里能获取到。
难道@@identity只能在insert里获取?
那我要在update,delete的触发器里获取唯一标识,如何做?
(PersonInfo表里有一个[id] int identity primary key)
FOR UPDATE
AS
begin
declare @id int
set @id = @@identity --更新记录的时候,获取不到???
print @id
declare @uname varchar(20),@nickname varchar(20),@pwd char(32),@gender bit,@regip varchar(15),@regdate smalldatetime,@lastip varchar(15),
@lastlogintime datetime,@account decimal,@email varchar(50),@bday char(10),
@qq varchar(12),@msn varchar(40),@realname nvarchar(10),@idcard varchar(20),@mobile varchar(20),@tel varchar(20),@homepage varchar(80)
--获取记录
select @uname=PI_UserName,@nickname=PI_NickName,@pwd=PI_password,@gender=PI_gender,@regip=PI_RegisterIP,@regdate=PI_registerTime,@lastip=PI_lastLoginIp,
@lastlogintime=PI_LastLoginTime,@account=PI_account,@email=PI_email,@bday=PI_Birthday --主表数据
,@qq=PI_QQ,@msn=PI_msn,@realname=PI_realname,@idcard=PI_idcard,@mobile=PI_mobile,@tel=PI_tel,@homepage=PI_homepage --扩展表数据
from dbo.PersonInfo where id=@id
begin tran
--同步主表
update dnt_users set username=@uname,nickname=@nickname,[password]=@pwd,gender=@gender,regip=@regip,joindate=@regdate,lastip=@lastip,lastvisit=@lastlogintime,credits=@account,email=@email,bday=@bday where PI_ID=@id
--同步扩展表
update dnt_userfields set qq=@qq,msn=@msn,realname=@realname,idcard=@idcard,mobile=@mobile,phone=@tel,website=@homepage where PI_ID=@id
commit tran
end
GOset @id = @@identity --更新记录的时候,获取不到???
在insert的触发器里能获取到。
难道@@identity只能在insert里获取?
那我要在update,delete的触发器里获取唯一标识,如何做?
(PersonInfo表里有一个[id] int identity primary key)
解决方案 »
- 求一SQL语句,分组中的限行~!
- 无法解决 equal to 操作中 "Chinese_PRC_CI_AS" 和 "Latin1_General_CI_AS" 之间的排序规则冲突。
- Why did my sqlcmd -L output nothing?
- 存储过程,可以这样写两个if语句吗
- ms sql server 2005 运行在完全恢复模式,日志文件是如何管理的?生成的事务日志如何归档的呢?
- 请教如何搞定这个问题。3X!
- 请教一下这条SQL语句
- sql server 2000中输入信息时出错,急
- MSSQL-用差异数据库备份还原时出现的问题
- 急!关于群集的问题
- 设置sqlserver2005的有关命令是什么?请高手指点
- update test set a=a+1 where...此语句稳定性
返回最后插入的标识值。
-->
跟更新应该没有关系
那我要在update,delete的触发器里获取唯一标识,如何做?
@@identity 只是当前会话中有插入时才有值
你有触发器的话.可以从虚拟表deleted或inserted中获取
set @id = id from inserted触发器我不太会哦
FOR UPDATE
AS
begin
begin tran
--同步主表
update dnt_users set username=b.PI_UserName,nickname=b.PI_NickName,[password]=b.PI_password,
gender=b.PI_gender,regip=b.PI_RegisterIP,joindate=b.PI_registerTime,lastip=b.PI_lastLoginIp,lastvisit=b.PI_LastLoginTime,
credits=b.PI_account,email=b.PI_email,bday=b.PI_Birthday
from dnt_users as a
inner join dbo.PersonInfo b on a.PI_ID=b.id
where a.PI_ID in (select id from inserted )
--同步扩展表
update dnt_userfields set qq=b.PI_QQ,msn=b.PI_msn,realname=b.PI_realname,idcard=b.PI_idcard,
mobile=b.PI_mobile,phone=b.PI_tel,website=b.PI_homepage
from dnt_userfields as a inner join dbo.PersonInfo b on a.PI_ID=b.id
where a.PI_ID IN (select id from inserted)
commit tranend
触发器应该有前触发和后触发之分吧!
也就是说若此次连接你没有插入,@@identity就没有值,IDENT_CURRENT('表名')是所有会话而且指定表名,也就是说即使最后插入是由别人引起的你也会得到
不过问一下高手,inserted表能存活多长时间呢?