现在有一个building表,里边有三个字段,building_id,building_name,building_name.
Field Type Collation Null Key Default
--------------- ----------- --------------- ------ ------ -------
building_id int(11) (NULL) NO UNI (NULL)
building_number int(11) (NULL) NO PRI (NULL)
building_name varchar(32) utf8_general_ci NO PRI (NULL)
还有另外一个表sql-write,里边有三个字段id(自增的),operation(varchar型),time(时间戳)
Field Type Collation Null Key Default Extra
--------- ------------ --------------- ------ ------ ------- --------------
id int(11) (NULL) NO PRI (NULL) auto_increment
operation varchar(100) utf8_general_ci YES (NULL)
time timestamp (NULL) YES (NULL)
现在我的目的是:
1.在sql-write表上创建触发器
2.当building表中有记录删除时,把相应的sql语句写入sql-write中,我的sql代码如下 DELIMITER $$CREATE
TRIGGER `testtrigger`.`delete_building` AFTER DELETE
ON `testtrigger`.`sql-write`
FOR EACH ROW BEGIN
declare @building_id,
set @building_id=OLD.building_id;
insert into sql-write set operation='delete from building where building_id=' old.building_id and time=now();
END$$DELIMITER ;
注:building表中building_id是主键,所以写入sql-write中的sql语句where后边的条件是building_id=old.building_id的我感觉思路是对的,就是不知道语法哪里错了?
Field Type Collation Null Key Default
--------------- ----------- --------------- ------ ------ -------
building_id int(11) (NULL) NO UNI (NULL)
building_number int(11) (NULL) NO PRI (NULL)
building_name varchar(32) utf8_general_ci NO PRI (NULL)
还有另外一个表sql-write,里边有三个字段id(自增的),operation(varchar型),time(时间戳)
Field Type Collation Null Key Default Extra
--------- ------------ --------------- ------ ------ ------- --------------
id int(11) (NULL) NO PRI (NULL) auto_increment
operation varchar(100) utf8_general_ci YES (NULL)
time timestamp (NULL) YES (NULL)
现在我的目的是:
1.在sql-write表上创建触发器
2.当building表中有记录删除时,把相应的sql语句写入sql-write中,我的sql代码如下 DELIMITER $$CREATE
TRIGGER `testtrigger`.`delete_building` AFTER DELETE
ON `testtrigger`.`sql-write`
FOR EACH ROW BEGIN
declare @building_id,
set @building_id=OLD.building_id;
insert into sql-write set operation='delete from building where building_id=' old.building_id and time=now();
END$$DELIMITER ;
注:building表中building_id是主键,所以写入sql-write中的sql语句where后边的条件是building_id=old.building_id的我感觉思路是对的,就是不知道语法哪里错了?
building_id=old.building
我纠结的是如何把删除的数据中的building_id存起来
不知道我说的是否清楚,麻烦您了
多谢!!!!
TRIGGER `testtrigger`.`delete_building` AFTER DELETE
ON `testtrigger`.`sql-write`
FOR EACH ROW BEGIN
insert into sql-write set (operation,time) values (concat('delete from building where building_id=', old.building_id),now());
END$$DELIMITER ;
TRIGGER `testtrigger`.`delete_building` AFTER DELETE
ON `testtrigger`.`sql-write`
FOR EACH ROW BEGIN
insert into sql-write set (operation,optime) values (concat('delete from building where building_id=', old.building_id),now());
END$$DELIMITER ;
为什么呢?多谢
insert into sqlwrite (operation,optime) values (concat('insert into building values(',new.building_id,',',new.building_number,',',''',new.building_name,'')'),now());sql-write表中operation字段的结果却是insert into building values(44,4444,',new.building_name,')
这是为什么呢?那个单引号怎么处理?
多谢!