tableA (AUTOID,INID,CCODE,CTEXT)
1 1 001 001text
2 2 030 030text
...
tableB (AUTOID,CCODE)
1 001
2 002
...求插入TABLE B后,产生对应TABLE A的记录
帮忙修改!CREATE TRIGGER ADDCODE ON TABLEB
for INSERT
AS
DECLARE @CCODE varchar(20) --TABLEA.CCODE
@INID INT --TABLEA.INID
IF EXISTS(SELECT 1 FROM INSERTED WHERE ...)
BEGIN
SET @CCODE=INSERTED.CCODE
SET @INID=SELECT MAX(INID) FROM TABLEA
insert into TABLEA
(INID,CCODE,CTEXT)
values
(@INID+1,@CCODE,@CCODE+'TEXT')
end
1 1 001 001text
2 2 030 030text
...
tableB (AUTOID,CCODE)
1 001
2 002
...求插入TABLE B后,产生对应TABLE A的记录
帮忙修改!CREATE TRIGGER ADDCODE ON TABLEB
for INSERT
AS
DECLARE @CCODE varchar(20) --TABLEA.CCODE
@INID INT --TABLEA.INID
IF EXISTS(SELECT 1 FROM INSERTED WHERE ...)
BEGIN
SET @CCODE=INSERTED.CCODE
SET @INID=SELECT MAX(INID) FROM TABLEA
insert into TABLEA
(INID,CCODE,CTEXT)
values
(@INID+1,@CCODE,@CCODE+'TEXT')
end
CREATE TRIGGER ADDCODE ON TABLEB
after INSERT
AS
DECLARE @CCODE varchar(20), --TABLEA.CCODE
@INID INT --TABLEA.INID
IF EXISTS(SELECT 1 FROM INSERTED WHERE ...)
BEGIN
SET @CCODE=INSERTED.CCODE
select @INID=MAX(INID)FROM TABLEA
insert into TABLEA
(INID,CCODE,CTEXT)
values
(@INID+1,@CCODE,@CCODE+'TEXT')
end
create table table1(autoid int,inid int,ccode varchar(20),ctext varchar(20))
insert into table1
select 1,1,'001','001text' union all
select 2,2,'030','030text'
gocreate table table2(autoid int,ccode varchar(20))
insert into table2
select 1,'001' union all
select 2,'002'
go
--drop table table1,table2
--drop trigger ADDCODE
CREATE TRIGGER ADDCODE ON TABLE2
after INSERT
AS
DECLARE @CCODE varchar(20), --TABLEA.CCODE
@INID INT --TABLEA.INID
IF EXISTS(SELECT ccode FROM INSERTED)
BEGIN
SET @CCODE=(select ccode from inserted)
select @INID=MAX(INID)FROM TABLE1
insert into TABLE1
(autoid,INID,CCODE,CTEXT)
values
(@INID+1,@INID+1,@CCODE,@CCODE+'TEXT')
end insert into table2 values(3,'003');select * from table1
select * from table2
autoid inid ccode ctext
----------- ----------- -------------------- --------------------
1 1 001 001text
2 2 030 030text
3 3 003 003TEXT(所影响的行数为 3 行)autoid ccode
----------- --------------------
1 001
2 002
3 003(所影响的行数为 3 行)