【高手请进】触发器

[cfderp]

麻烦高手帮忙，还是触发器的问题，A、B两张表，A表有id、xingming（姓名）、bianhao（编号）、zuoyeliang（作业量）。B表字段和A表一样。当A表新增一条记录时，B表同样增加一条记录，A表新增的记录删除时B表触发过来的记录不变。当A表只修改一条记录的“作业量”字段的数据时B表触发过来的相应的记录同样修改“作业量”字段数据。当A表修改一条记录的“姓名”、“编号”字段内容时，B表新增一条同样的记录。麻烦大家帮忙了，触发器不太明白，谢谢大家

[htl258_Tony]

CREATE TRIGGER tr_test ON a
FOR  INSERT, UPDATE
AS
	IF NOT EXISTS(
	       SELECT 1 
	       FROM DELETED
	   )
	   OR 
	    UPDATE (xingming) OR UPDATE (bianhao)
	--以下考虑ID自增，如果ID非自增列，则加上ID字段。
	INSERT b( xingming, bianhao, zuoyeliang )
		SELECT xingming, bianhao, zuoyeliang 
		FROM INSERTED
	
	IF UPDATE (zuoyeliang)
		UPDATE b
		SET    b.zuoyeliang = i.zuoyeliang
		FROM   INSERTED i
		WHERE  b.id = i.id
GO

[htl258_Tony]

[Quote=引用 4 楼 cfderp 的回复:]
CREATE TRIGGER tr_test ON a
FOR INSERT, UPDATE
AS
IF NOT EXISTS(
SELECT 1
FROM DELETED
)
OR
UPDATE (xingming) OR UPDATE (bianhao)
--以下考虑ID自增，如果ID非自增列，则加上ID字段。
INSERT b( xin……
[/Quote]

CREATE TRIGGER tr_test ON a
FOR  INSERT, UPDATE
AS
    IF NOT EXISTS(
           SELECT 1 
           FROM DELETED
       )
       OR 
        UPDATE (xingming) OR UPDATE (bianhao)
    --以下考虑ID自增，如果ID非自增列，则加上ID字段。
    INSERT b( xingming, bianhao, zuoyeliang )
        SELECT xingming, bianhao, zuoyeliang 
        FROM INSERTED i
        WHERE NOT EXISTS(
                  SELECT 1 
                  FROM b
                  WHERE id = i.id
                  	AND CHECKSUM(bianhao, zuoyeliang) = CHECKSUM(i.bianhao, i.zuoyeliang)
              )
    
    IF UPDATE (zuoyeliang)
        UPDATE b
        SET    b.zuoyeliang = i.zuoyeliang
        FROM   INSERTED i
        WHERE  b.id = i.id
GO

try again

[Colerterry]

顶，学习了！

[cfderp]

CREATE TRIGGER tr_test ON a
FOR INSERT, UPDATE
AS
IF NOT EXISTS(
SELECT 1
FROM DELETED
)
OR
UPDATE (xingming) OR UPDATE (bianhao)
--以下考虑ID自增，如果ID非自增列，则加上ID字段。
INSERT b( xingming, bianhao, zuoyeliang )
SELECT xingming, bianhao, zuoyeliang
FROM INSERTED

IF UPDATE (zuoyeliang)
UPDATE b
SET b.zuoyeliang = i.zuoyeliang
FROM INSERTED i
WHERE b.id = i.id
GO
首先感谢htl258的帮忙，不过UPDATE (xingming) OR UPDATE (bianhao)这条语句有点问题，就是即使xingming和bianhao的值没变化也插入了一条语句。请大家帮忙啊

[v1ctory1216]

加个括号试一下(upadata(xingming) or upadata(bianhao))

[claro]

[Quote=引用 1 楼 htl258 的回复:]
SQL code
CREATE TRIGGER tr_test ON a
FOR INSERT, UPDATE
AS
IF NOT EXISTS(
SELECT 1
FROM DELETED
)
OR
UPDATE (xingming) OR UPDATE (bianhao)
……
[/Quote]顶

[cfderp]

感谢htl258的帮助，谢谢你的指点