创建一个触发事件

[༺ཌༀཉི浮华背后༃ༀད༻]

tableA:
aid name
1   tom
2   node

tableB:
bid weight aid date
1   1.5    1
当对tableA中的aid进行增、删、改时，tableB中的aid对应的进行增、删、改。

自己写了个，但是错的：
CREATE TRIGGER testref AFTER INSERT ON tableA
  FOR EACH ROW BEGIN
    INSERT INTO tableB AS w SET w.nid = NEW.nid, w.`date`= NOW();
  END
|
AFTER UPDATE ON tableA
FOR EACH ROW
BEGIN 
  UPDATE tableB SET w.nid = NEW.nid WHERE w.nid = OLD.nid;
END
|
AFTER DELETE ON ncnode
FOR EACH ROW 
BEGIN
  DELETE FROM ncweight WHERE w.nid=NEW.nid;
END;

[WWWWA]

写3个TRIGGER

[༺ཌༀཉི浮华背后༃ༀད༻]

能不能给点代码实例？我也知道要写三次啊

[༺ཌༀཉི浮华背后༃ༀད༻]

那个update怎么写，判断时用OLD.id和更改时用NEW.id,这样对吗？

[ACMAIN_CHM]

分别写三个，一个一个写，三个 create trigger 不要混在一起。

[rucypli]

CREATE TRIGGER testref1 AFTER INSERT ON tableA
FOR EACH ROW BEGIN
INSERT INTO tableB AS w SET w.nid = NEW.nid, w.`date`= NOW();
END
CREATE TRIGGER testref2
AFTER UPDATE ON tableA
FOR EACH ROW
BEGIN
UPDATE tableB SET w.nid = NEW.nid WHERE w.nid = OLD.nid;
END
CREATE TRIGGER testref3
AFTER DELETE ON ncnode
FOR EACH ROW
BEGIN
DELETE FROM ncweight WHERE w.nid=NEW.nid;
END;

[wwwwb]

[Quote=引用 1 楼 的回复:]

写3个TRIGGER
[/Quote]
分开写，不要写在一起

[༺ཌༀཉི浮华背后༃ༀད༻]

原表1：
CREATE TABLE `ncnode` (
   `nid` int(6) NOT NULL AUTO_INCREMENT,
   `name` varchar(20) NOT NULL DEFAULT '' COMMENT '名称',
   `type` enum('net','computer','user','apply','data') NOT NULL DEFAULT 'net' COMMENT '类型',
   `CDT` varchar(30) NOT NULL DEFAULT '' COMMENT '保密性',
   `IGT` varchar(30) NOT NULL DEFAULT '' COMMENT '完整性',
   `AAT` varchar(30) NOT NULL DEFAULT '' COMMENT '可用性',
   `reserve` text COMMENT '保留',
   PRIMARY KEY (`nid`)
 ) ENGINE=InnoDB AUTO_INCREMENT=22 DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC

show：

FIELD    TYPE                                          COLLATION        NULL    KEY     DEFAULT  Extra           PRIVILEGES                       COMMENT  
-------  --------------------------------------------  ---------------  ------  ------  -------  --------------  -------------------------------  ---------
nid      INT(6)                                        (NULL)           NO      PRI     (NULL)   AUTO_INCREMENT  SELECT,INSERT,UPDATE,REFERENCES           
NAME     VARCHAR(20)                                   utf8_general_ci  NO                                       SELECT,INSERT,UPDATE,REFERENCES  名称   
TYPE     ENUM('net','computer','user','apply','data')  utf8_general_ci  NO              net                      SELECT,INSERT,UPDATE,REFERENCES  类型   
CDT      VARCHAR(30)                                   utf8_general_ci  NO                                       SELECT,INSERT,UPDATE,REFERENCES  保密性
IGT      VARCHAR(30)                                   utf8_general_ci  NO                                       SELECT,INSERT,UPDATE,REFERENCES  完整性
AAT      VARCHAR(30)                                   utf8_general_ci  NO                                       SELECT,INSERT,UPDATE,REFERENCES  可用性
reserve  TEXT                                          utf8_general_ci  YES             (NULL)                   SELECT,INSERT,UPDATE,REFERENCES  保留   

原表2：
CREATE TABLE `ncweight` (
  `wid` INT(6) NOT NULL AUTO_INCREMENT,
  `weight` INT(3) NOT NULL DEFAULT '1',
  `date` VARCHAR(20) NOT NULL DEFAULT '',
  `nid` INT(6) NOT NULL DEFAULT '1',
  PRIMARY KEY (`wid`)
) ENGINE=INNODB DEFAULT CHARSET=utf8 CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC


show：
FIELD   TYPE         COLLATION        NULL    KEY     DEFAULT  Extra           PRIVILEGES                       COMMENT
------  -----------  ---------------  ------  ------  -------  --------------  -------------------------------  -------
wid     INT(6)       (NULL)           NO      PRI     (NULL)   AUTO_INCREMENT  SELECT,INSERT,UPDATE,REFERENCES         
weight  INT(3)       (NULL)           NO              1                        SELECT,INSERT,UPDATE,REFERENCES         
DATE    VARCHAR(20)  utf8_general_ci  NO                                       SELECT,INSERT,UPDATE,REFERENCES         
nid     INT(6)       (NULL)           NO              1                        SELECT,INSERT,UPDATE,REFERENCES         

tiggers：
CREATE TRIGGER testref1 AFTER INSERT ON ncnode
  FOR EACH ROW 
  BEGIN
  INSERT INTO ncweight AS w SET w.nid = NEW.nid, w.`date`= NOW();
  END;
  
CREATE TRIGGER testref2 AFTER UPDATE ON ncnode
FOR EACH ROW
BEGIN 
  UPDATE ncweight SET w.nid = NEW.nid WHERE w.nid = OLD.nid;
END;

CREATE TRIGGER testref3 AFTER DELETE ON ncnode
FOR EACH ROW 
BEGIN
  DELETE FROM ncweight WHERE w.nid=NEW.nid;
END;

错误提示：
错误码: 1363
There is no NEW row in on DELETE trigger

怎么解决？

[༺ཌༀཉི浮华背后༃ༀད༻]

这样也不行:

CREATE TRIGGER testref1 AFTER INSERT ON ncnode
  FOR EACH ROW 
  BEGIN
  INSERT INTO ncweight SET ncweight.nid = NEW.nid, ncweight.`date`= NOW();
  END;
  
CREATE TRIGGER testref2 AFTER UPDATE ON ncnode
FOR EACH ROW
BEGIN 
  UPDATE ncweight SET ncweight.nid = NEW.nid WHERE ncweight.nid = OLD.nid;
END;

CREATE TRIGGER testref3 AFTER DELETE ON ncnode
FOR EACH ROW 
BEGIN
  DELETE FROM ncweight WHERE ncweight.nid=NEW.nid;
END;

错误码: 1363
There is no NEW row in on DELETE trigger

[wwwwb]

NEW->OLD

[༺ཌༀཉི浮华背后༃ༀད༻]

CREATE TRIGGER testref1 AFTER INSERT ON ncnode
  FOR EACH ROW 
  BEGIN
  INSERT INTO ncweight SET ncweight.nid = NEW.nid, ncweight.`date`= NOW();
  END;
  
CREATE TRIGGER testref2 AFTER UPDATE ON ncnode
FOR EACH ROW
BEGIN 
  UPDATE ncweight SET ncweight.nid = NEW.nid WHERE ncweight.nid = OLD.nid;
END;

CREATE TRIGGER testref3 AFTER DELETE ON ncnode
FOR EACH ROW 
BEGIN
  DELETE FROM ncweight WHERE ncweight.nid=OLD.nid;
END;

错误码: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 4

[wwwwb]

delimiter $$
CREATE TRIGGER testref1 AFTER INSERT ON ncnode
FOR EACH ROW
BEGIN
INSERT INTO ncweight SET ncweight.nid = NEW.nid, ncweight.`date`= NOW();
END$$
delimiter ;

其它的自己修改

[༺ཌༀཉི浮华背后༃ༀད༻]

解决了:

1、
DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `ncmodel`.`trigInsert` AFTER INSERT
    ON `ncmodel`.`ncnode`
    FOR EACH ROW BEGIN
	 INSERT INTO ncweight SET ncweight.nid = NEW.nid, ncweight.`date`= NOW();
    END$$

DELIMITER ;

2、
DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `ncmodel`.`nodeUpdate` AFTER UPDATE
    ON `ncmodel`.`ncnode`
    FOR EACH ROW BEGIN
	UPDATE ncweight SET ncweight.nid = NEW.nid WHERE ncweight.nid = OLD.nid;
    END$$

DELIMITER ;

3、
DELIMITER $$

CREATE
    /*[DEFINER = { user | CURRENT_USER }]*/
    TRIGGER `ncmodel`.`nodeDelete` AFTER DELETE
    ON `ncmodel`.`ncnode`
    FOR EACH ROW BEGIN
	DELETE FROM ncweight WHERE ncweight.nid=OLD.nid;
    END$$

DELIMITER ;

原来sqlyog中就有图形化创建trigger的地方，很方便，现在才发现，晕菜了。
O(∩_∩)O谢谢各位的帮助。