{"id":1582,"date":"2022-11-18T23:29:27","date_gmt":"2022-11-18T15:29:27","guid":{"rendered":"https:\/\/qaqaq.top\/?p=1582"},"modified":"2022-11-27T12:40:58","modified_gmt":"2022-11-27T04:40:58","slug":"%e5%88%9b%e5%bb%ba%e8%a7%a6%e5%8f%91%e5%99%a8%e3%80%81%e6%9f%a5%e7%9c%8b%e5%88%a0%e9%99%a4%e8%a7%a6%e5%8f%91%e5%99%a8","status":"publish","type":"post","link":"https:\/\/qaqaq.top\/?p=1582","title":{"rendered":"\u521b\u5efa\u89e6\u53d1\u5668\u3001\u67e5\u770b\u5220\u9664\u89e6\u53d1\u5668"},"content":{"rendered":"\n<pre class=\"wp-block-code\"><code># \u7b2c17\u7ae0_\u89e6\u53d1\u5668\r\n\r\n# 0. \u51c6\u5907\u5de5\u4f5c\r\nCREATE DATABASE dbtest17;\r\n\r\nUSE dbtest17;\r\n\r\n#1. \u521b\u5efa\u89e6\u53d1\u5668\r\n\r\n#\u4e3e\u4f8b1\uff1a\r\n# \u2460 \u521b\u5efa\u6570\u636e\u8868\r\n\r\nCREATE TABLE test_trigger (\r\n    id INT PRIMARY KEY AUTO_INCREMENT,\r\n    t_note VARCHAR(30)\r\n);\r\n\r\nCREATE TABLE test_trigger_log (\r\n    id INT PRIMARY KEY AUTO_INCREMENT,\r\n    t_log VARCHAR(30)\r\n);\r\n\r\n# \u2461 \u67e5\u770b\u8868\u6570\u636e\r\nSELECT * FROM test_trigger;\r\n\r\nSELECT * FROM test_trigger_log;\r\n\r\n# \u2462 \u521b\u5efa\u89e6\u53d1\u5668\r\n# \u521b\u5efa\u89e6\u53d1\u5668\uff1a\u521b\u5efa\u540d\u79f0\u4e3abefore_insert_test_tri\u7684\u89e6\u53d1\u5668\uff0c\u5411test_trigger\u6570\u636e\u8868\u63d2\u5165\u6570\u636e\u4e4b\u524d\uff0c\r\n# \u5411test_trigger_log\u6570\u636e\u8868\u4e2d\u63d2\u5165before_insert\u7684\u65e5\u5fd7\u4fe1\u606f\u3002\r\n\r\nDELIMITER \/\/\r\n\r\nCREATE TRIGGER before_insert_test_tri\r\n    BEFORE INSERT ON test_trigger\r\n    FOR EACH ROW\r\n    BEGIN\r\n        INSERT INTO test_trigger_log(t_log)\r\n            VALUES('before insert...');\r\n    END \/\/\r\n\r\nDELIMITER ;\r\n\r\n# \u2463 \u6d4b\u8bd5\r\nINSERT INTO test_trigger(t_note)\r\nVALUES('Tom...');\r\n\r\nSELECT * FROM test_trigger;\r\n\r\nSELECT * FROM test_trigger_log;\r\n\r\n#\u4e3e\u4f8b2\uff1a\r\n# \u521b\u5efa\u540d\u79f0\u4e3aafter_insert\u7684\u89e6\u53d1\u5668\uff0c\u5411test_trigger\u6570\u636e\u8868\u63d2\u5165\u6570\u636e\u4e4b\u540e\uff0c\r\n# \u5411test_trigger_log\u6570\u636e\u8868\u4e2d\u63d2\u5165after_insert\u7684\u65e5\u5fd7\u4fe1\u606f\u3002\r\n\r\nDELIMITER $\r\n\r\nCREATE TRIGGER after_insert_test_tri\r\n    AFTER INSERT ON test_trigger\r\n    FOR EACH ROW\r\n    BEGIN\r\n        INSERT INTO test_trigger_log(t_log)\r\n            VALUES('after insert...');\r\n    END $\r\n\r\nDELIMITER ;\r\n\r\n#\u6d4b\u8bd5\r\nINSERT INTO test_trigger(t_note)\r\nVALUES('Jerry2...');\r\n\r\nSELECT * FROM test_trigger;\r\n\r\nSELECT * FROM test_trigger_log;\r\n\r\n#\u4e3e\u4f8b3\uff1a\r\n# \u5b9a\u4e49\u89e6\u53d1\u5668\u201csalary_check_trigger\u201d\uff0c\u57fa\u4e8e\u5458\u5de5\u8868\u201cemployees\u201d\u7684INSERT\u4e8b\u4ef6\uff0c\r\n# \u5728INSERT\u4e4b\u524d\u68c0\u67e5\u5c06\u8981\u6dfb\u52a0\u7684\u65b0\u5458\u5de5\u85aa\u8d44\u662f\u5426\u5927\u4e8e\u4ed6\u9886\u5bfc\u7684\u85aa\u8d44\uff0c\u5982\u679c\u5927\u4e8e\u9886\u5bfc\u85aa\u8d44\uff0c\r\n# \u5219\u62a5sqlstate_value\u4e3a'HY000'\u7684\u9519\u8bef\uff0c\u4ece\u800c\u4f7f\u5f97\u6dfb\u52a0\u5931\u8d25\u3002\r\n\r\n#\u51c6\u5907\u5de5\u4f5c\r\nCREATE TABLE employees\r\nAS\r\n    SELECT * FROM atguigudb.employees;\r\n\r\nCREATE TABLE departments\r\nAS\r\n    SELECT * FROM atguigudb.employees;\r\n\r\nDESC employees;\r\n\r\n#\u521b\u5efa\u89e6\u53d1\u5668\r\nDELIMITER \/\/\r\n\r\nCREATE TRIGGER salary_check_trigger\r\n    BEFORE INSERT ON employees\r\n    FOR EACH ROW\r\n    BEGIN\r\n        #\u67e5\u8be2\u5230\u8981\u6dfb\u52a0\u7684\u6570\u636e\u7684manager\u7684\u85aa\u8d44\r\n        DECLARE mgr_sal DOUBLE;\r\n\r\n        SELECT salary INTO mgr_sal FROM employees\r\n        WHERE employee_id = NEW.manager_id;\r\n\r\n        IF NEW.salary > mgr_sal\r\n            THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '\u85aa\u8d44\u9ad8\u4e8e\u9886\u5bfc\u85aa\u8d44\u9519\u8bef';\r\n            END IF;\r\n\r\n    END \/\/\r\n\r\nDELIMITER ;\r\n\r\n#\u6d4b\u8bd5\r\nDESC employees;\r\n\r\n#\u6dfb\u52a0\u6210\u529f\uff1a\u4f9d\u7136\u89e6\u53d1\u4e86\u89e6\u53d1\u5668salary_check_trigger\u7684\u6267\u884c\r\nINSERT INTO employees(employee_id,last_name,email,hire_date,job_id,salary,manager_id)\r\nVALUES(300,'Tom','tom@126.com',CURDATE(),'AD_VP',8000,103);\r\n\r\nINSERT INTO employees(employee_id,last_name,email,hire_date,job_id,salary,manager_id)\r\nVALUES(301,'Tom1','tom1@126.com',CURDATE(),'AD_VP',10000,103);\r\n\r\nSELECT * FROM employees;\r\n\r\n# 2. \u67e5\u770b\u89e6\u53d1\u5668\r\n# \u2460 \u65b9\u5f0f3\uff1a\u67e5\u770b\u5f53\u524d\u6570\u636e\u5e93\u7684\u6240\u6709\u89e6\u53d1\u5668\u7684\u5b9a\u4e49\r\nSHOW TRIGGERS;\r\n\r\n# \u2461 \u65b9\u5f0f3\uff1a\u67e5\u770b\u5f53\u524d\u6570\u636e\u5e93\u4e2d\u67d0\u4e2a\u89e6\u53d1\u5668\u7684\u5b9a\u4e49\r\nSHOW CREATE TRIGGER salary_check_trigger;\r\n\r\n# \u2462 \u65b9\u5f0f3\uff1a\u4ece\u7cfb\u7edf\u5e93information_schema\u7684TRIGGERS\u8868\u4e2d\u67e5\u8be2\u201csalary_check_trigger\u201d\u89e6\u53d1\u5668\u7684\u4fe1\u606f\u3002\r\nSELECT * FROM information_schema.TRIGGERS;\r\n\r\n# 3. \u5220\u9664\u89e6\u53d1\u5668\r\nDROP TRIGGER IF EXISTS after_insert_test_tri;\r\n\r\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[47],"class_list":["post-1582","post","type-post","status-publish","format-standard","hentry","category-mysql-code","tag-mysql"],"_links":{"self":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1582"}],"collection":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1582"}],"version-history":[{"count":1,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1582\/revisions"}],"predecessor-version":[{"id":1583,"href":"https:\/\/qaqaq.top\/index.php?rest_route=\/wp\/v2\/posts\/1582\/revisions\/1583"}],"wp:attachment":[{"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1582"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1582"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/qaqaq.top\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1582"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}