SQL server Update触发器怎么写

Question

A表：odmaters，B表：projects，C表：sctoerpxq
A表字段：odcode，materid，unitct
B表字段：sdate，res，tel3
C表字段：projectid, materid, unitct, sdate, res,version
对应关系 ：odcode=projectid   materid=materid   
unitct=unitct   sdate=sdate  res=res
tel3 = version
在A表中建立Update触发器 使A表发生数据的变化 C表相应的改变
A表odcode字段与B表的projectid指端相关联

Answer 1

CREATE TRIGGER dbo.test_tri ON [dbo].odmaters
    FOR UPDATE
AS
    BEGIN
        UPDATE  sctoerpxq
        SET     materid = t.materid ,
                unitct = t.unitct
        FROM    ( SELECT    *
                  FROM      Inserted
                ) t
        WHERE   t.odcode = projectid
       
    END
GO

Answer 2

A表变化更新C表；那就是C表中原来就有数据；A表和B表没有关联吧

Answer 3

我同时要将B表的一些字段更新到C表     A表odcode字段与B表的projectid一样  可以作关联

Answer 4

create trigger trigger_sync_odmaters_sctoerpxq on odmaters after insert
as 
 insert into sctoerpxq(projectid, materid, unitct, sdate, res,version) 
 select inserted.odcode, inserted.materid, inserted.unitct, projects.sdate, projects.res,projects.tel3
  from inserted, projects 
  where inserted.odcode=projects.projectid这是我写的insert触发器

Answer 5

B表没有peojecrid 啊

Answer 6

能不能那样写
只要A表出现一点点变化  我就删除C表所有数据  然后运行
insert into sctoerpxq(projectid, materid, unitct, sdate, res,version)
select projects.projectid,odmaters.materid,odmaters.unitct,projects.sdate,projects.res ,projects.tel3
from dbo.projects,dbo.odmaters 
where projects.projectid=odmaters.odcode
这个语句

Answer 7

CREATE TRIGGER dbo.test_tri ON [dbo].odmaters
    FOR UPDATE
AS
    BEGIN
        DELETE  FROM sctoerpxq        INSERT  INTO sctoerpxq
                ( projectid ,
                  materid ,
                  unitct ,
                  sdate ,
                  res ,
                  version
                )
                SELECT  projects.projectid ,
                        odmaters.materid ,
                        odmaters.unitct ,
                        projects.sdate ,
                        projects.res ,
                        projects.tel3
                FROM    dbo.projects ,
                        dbo.odmaters
                WHERE   projects.projectid = odmaters.odcode
    END
GO

Answer 8

但是这样写就是A表一变就把所有的C表都删除了，然后重新插入；这样效率不高，数据多了会有问题，可以加一个ID判断，只删除变了ID的C表数据，插入对应Id的数据到C表

Answer 9

这样写
CREATE TRIGGER dbo.test_tri ON [dbo].odmaters
    FOR UPDATE
AS
    BEGIN
        DELETE  FROM sctoerpxq
        WHERE   projectid IN ( SELECT   *
                               FROM     Inserted )        INSERT  INTO sctoerpxq
                ( projectid ,
                  materid ,
                  unitct ,
                  sdate ,
                  res ,
                  version
                )
                SELECT  projects.projectid ,
                        odmaters.materid ,
                        odmaters.unitct ,
                        projects.sdate ,
                        projects.res ,
                        projects.tel3
                FROM    dbo.projects ,
                        dbo.odmaters
                WHERE   projects.projectid = odmaters.odcode
                        AND projectid IN ( SELECT   *
                                           FROM     Inserted )
    END
GO

Answer 10

sorry……这样试试
CREATE TRIGGER dbo.test_tri ON [dbo].odmaters
    FOR UPDATE
AS
    BEGIN
        DELETE  FROM sctoerpxq
        WHERE   projectid IN ( SELECT   projectid
                               FROM     Inserted )        INSERT  INTO sctoerpxq
                ( projectid ,
                  materid ,
                  unitct ,
                  sdate ,
                  res ,
                  version
                )
                SELECT  projects.projectid ,
                        odmaters.materid ,
                        odmaters.unitct ,
                        projects.sdate ,
                        projects.res ,
                        projects.tel3
                FROM    dbo.projects ,
                        dbo.odmaters
                WHERE   projects.projectid = odmaters.odcode
                        AND projectid IN ( SELECT   projectid
                                           FROM     Inserted )
    END
GO