表A如下
id name bank account salary
1 AAA IC 1111 200
2 BBB AB 2222 300
id 自动递增，插入一条新记录与AAA同名，则自动添加bank, account. 变成如下
id name bank account salary
1 AAA IC 1111 200
2 BBB AB 2222 300
3 AAA IC 1111 400
触发器怎么写？谢谢，谢谢
------解决方案--------------------
- SQL code
CREATE TABLE [dbo].[table1]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](50) NULL, [bank] [varchar](50) NULL, [account] [int] NULL, [salary] [float] NULL) ON [PRIMARY]GOinsert table1([name],[bank],[account],[salary])select 'AAA','IC',1111,200 union allselect 'BBB','AB',2222,300---------------------------------------------create trigger newRecInserton table1after insertasif exists (select top 1 * from table1,inserted where table1.name=inserted.name)begin declare @bank varchar(50),@account int select @bank=table1.bank,@account=table1.account from table1,inserted where table1.name=inserted.name and table1.bank is not null update table1 set [email protected],[email protected] from table1,inserted where table1.name=inserted.nameend---------------------------------------------insert table1([name],[salary])select 'AAA',400select * from table1drop table table1
------解决方案--------------------
- SQL code
create table taba(id int identity(1,1), name varchar(5), bank varchar(5), account varchar(5), salary int)insert into taba(name,bank,account,salary)select 'AAA', 'IC', '1111', 200 union allselect 'BBB', 'AB', '2222', 300create trigger tr_tabaon taba instead of insertasbegin insert into taba(name,bank,account,salary) select i.name, isnull(bank,(select top 1 bank from taba where name=i.name)), isnull(account,(select top 1 account from taba where name=i.name)), salary from inserted iendinsert into taba(name,salary) select 'AAA',400select * from taba/*id name bank account salary----------- ----- ----- ------- -----------1 AAA IC 1111 2002 BBB AB 2222 3003 AAA IC 1111 400(3 row(s) affected)*/