一张表同时建立触发器update和insert，如何判定执行条件

[touxiansinuo018956]

对一张表做两个触发器，分别是update和insert；
Insert的条件是目标表不存在源表的主键，则将一整行数据插入；
如果目标表存在源表的主键，且timestamp发生变化，则说明应该进行update操作，
Update发生改变的数据至目标表

create trigger trig_insert_transMore 
on transMore
for insert
as
if((transMore.priID)<>(transInfo.priID))
begin
insert into transInfo(priID,cardID,transType,transMoney )select priID,cardID,transType,transMoney from inserted
end

create trigger trig_update_transMore
on transMore
for update
as
if update(transDate)
--此处的判定条件该如何写,判断(transMore.priID)=(transInfo.priID),且transDate发生了改变
 declare @temp_transMoney money,@temp_priID nchar(10),@temp_cardID nchar(10),@temp_transType nchar(10)
 select @temp_transMoney=transMoney,@temp_priID=priID ,@temp_cardID=cardID,@temp_transType=transType from inserted 
 update transInfo 
 set 
 transMoney=@temp_transMoney,cardID=@temp_cardID,transType=@temp_transType
 where priID=@temp_priID
go

[touxiansinuo018956]

对于文字描述需求部分，恳求给出实现代码，下面是自己写的，不能实现，希望能指点出错误，附代码。

[Ginnnnnnnn]

按照你的逻辑，其实完全不用判断直接可以执行更新语句了，如果有的修改就更新

  create trigger trig_update_transMore
on transMore
for update
AS
if update(transDate) 
--此处的判定条件该如何写,判断(transMore.priID)=(transInfo.priID),且transDate发生了改变
 update a  SET 
 transMoney=b.transMoney,cardID=b.cardID,transType=b.transType
 FROM transInfo a
	INNER JOIN inserted b ON a.priID = b.priID 

go

[touxiansinuo018956]

where not exists(select 1 from transInfo b where b.priID=a.priID)这句是什么意思，select 1是？

[中国风]

create trigger trig_insert_transMore 
on transMore
for insert
AS
BEGIN
	
		INSERT into transInfo(priID,cardID,transType,transMoney )select i.priID,i.cardID,i.transType,i.transMoney 
		FROM inserted AS i 
			LEFT JOIN transInfo AS t ON i.priID=t.priID
		WHERE t.priID IS NULL --判断时可这样用
END 
GO

create trigger trig_update_transMore
on transMore
for update
AS
begin
--if update(transDate)--update transMore set transDate=transDate 也会触发
IF EXISTS(SELECT 1 FROM transMore AS t INNER JOIN inserted AS i ON i.priID=t.priID AND i.transDate<>t.transDate)
	UPDATE t SET transMoney=i.transMoney,cardID=i.cardID,transType=i.transType FROM transInfo AS t INNER JOIN inserted AS i ON i.priID=t.priID
END 
GO

[唐诗三百首]

create trigger trig_insert_transMore 
on transMore for insert
as
begin
   insert into transInfo(priID,cardID,transType,transMoney )
      select priID,cardID,transType,transMoney 
      from inserted a
      where not exists(select 1 from transInfo b where b.priID=a.priID)
end


create trigger trig_update_transMore
on transMore for update
as
begin
if update(transDate) 
   and exists(select 1 
                       from inserted a 
                       inner join transInfo b on a.priID=b.priID)
begin
    declare @temp_transMoney money,@temp_priID nchar(10),@temp_cardID nchar(10),@temp_transType nchar(10)
    
    select @temp_transMoney=transMoney,@temp_priID=priID ,@temp_cardID=cardID,@temp_transType=transType from inserted 

    update transInfo 
      set transMoney=@temp_transMoney,cardID=@temp_cardID,transType=@temp_transType
      where priID=@temp_priID
end
end