mysql触发器sql
1、首先写建立触发器的sql语句：
1）插入时的：
CREATE TRIGGER insertref BEFORE INSERT ON a
FOR EACH ROW BEGIN
UPDATE b SET registerStatus =1 WHERE NEW.id = id and NEW.received is not null and NEW.received !='';
update b set registerIP=NEW.received where NEW.id = id;
end
2)删除时：
CREATE TRIGGER deleteref BEFORE DELETE ON a
FOR EACH ROW begin
update b set registerStatus =0 WHERE OLD.id = id;
update b set registerIP=NULL where OLD.id = id;
end
3）更新时：
CREATE TRIGGER updateref BEFORE UPDATE ON a
FOR EACH ROW BEGIN
update b set registerIP=NEW.received where OLD.id=id;
end
❷ SQL server编写的触发器，用MySQL怎么编写
求人不如求己，建议你看下这篇文章，自己学会了比什么都强
Mysql 5.1 触发器语法
http://dev.mysql.com/doc/refman/5.1/zh/triggers.html
触发器3示例
http://blog.csdn.net/myron_sqh/article/details/16105255
❸ MySQL触发器怎么变相使用动态SQL
MySQL触发器动态sql： CREATE TRIGGER trigger1 after INSERT ON cdr FOR EACH ROW BEGIN DECLARE id BIGINT; DECLARE vname VARCHAR(200); DECLARE destination VARCHAR(200) DEFAULT 'null'; DECLARE rows1 BIGINT DEFAULT 0; DECLARE sql1 VARCHAR(2000); SELECT v.vendorid,v.name, ve.DestinationID INTO id, vname, destination FROM (select name, v.VendorID from vendor v natural join vendorendpoints where endpoint=NEW.Field27) v NATURAL JOIN (SELECT * FROM vendorextraction order by length desc) ve WHERE substr(NEW.Field8 , 1 ,ve.length)=substr(CONCAT(ve.Prefix,ve.OperatorList),1,ve.length) limit 0,1; SET @@sql_mode='ansi'; SET sql1 =CONCAT('insert into `',CONCAT(vname,id),'` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8,'","',NEW.Field0,'")'); IF (id >0) THEN call testing(sql1); ELSE SET sql1 =CONCAT('insert into `unknown` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8... MySQL触发器动态sql：
CREATE TRIGGER trigger1 after INSERT ON cdr
FOR EACH ROW
BEGIN
DECLARE id BIGINT;
DECLARE vname VARCHAR(200);
DECLARE destination VARCHAR(200) DEFAULT 'null';
DECLARE rows1 BIGINT DEFAULT 0;
DECLARE sql1 VARCHAR(2000);
SELECT v.vendorid,v.name, ve.DestinationID INTO id, vname, destination
FROM (select name, v.VendorID from vendor v natural join vendorendpoints where endpoint=NEW.Field27) v NATURAL JOIN (SELECT * FROM vendorextraction order by length desc) ve
WHERE
substr(NEW.Field8 , 1 ,ve.length)=substr(CONCAT(ve.Prefix,ve.OperatorList),1,ve.length) limit 0,1;
SET @@sql_mode='ansi';
SET sql1 =CONCAT('insert into `',CONCAT(vname,id),'` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8,'","',NEW.Field0,'")');
IF (id >0) THEN
call testing(sql1);
ELSE
SET sql1 =CONCAT('insert into `unknown` values(',id,',"',vname,'","',NEW.Field5,'","',NEW.Field35,'","',NEW.Field12,'","',NEW.Field8,'","',NEW.Field0,'")');
call testing(sql1);
END IF;
❹ mysql 数据库！！ 当更新一条记录status字段为2时,触发更新这个记录的flag字段为1。的触发器sql怎么写
DROP TRIGGER IF EXISTS update_on_tab1;
CREATE TRIGGER update_on_tab1
AFTER upate ON tab1
FOR EACH ROW
BEGIN
if (new.status=2) then
update tab1 set flag=1 where id=new.id;
end if;
END;
删除本触发器
drop trigger update_on_tab1;
❺ 在Mysql中，如何在触发器中捕获当前执行的SQL语句
目测语法是 Oracle 数据库的语法。
那么， 如果这个where aptitude.companyid=com_base.id;条件获得上面执行的com_base.id怎么获取啊？
可以通过 :old.id 或者 :new.id 来获取。
例如：
SQL> CREATE OR REPLACE TRIGGER BeforeUpdateTest
2 BEFORE UPDATE ON test_trigger_table
3 FOR EACH ROW
4 BEGIN
5 dbms_output.put_line('BEFORE UPDATE');
6 dbms_output.put_line('Old Name = ' || :old.name);
7 dbms_output.put_line('New Name = ' || :new.name);
8 END;
9 /
Trigger created.
SQL>
SQL> UPDATE test_trigger_table SET name = 'XYZ' WHERE id = 1;
BEFORE UPDATE
Old Name = ABC
New Name = XYZ