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