openGauss每日一练第18天 | 学习心得体会

触发器

一个对象进行DML操作的时候，就会触发一个事件，Let’s start!

1.练习

CREATE TABLE test_trigger_src_tbl(id1 INT, id2 INT, id3 INT) ;
CREATE TABLE test_trigger_des_tbl(id1 INT, id2 INT, id3 INT);

CREATE OR REPLACE FUNCTION tri_insert_func() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
INSERT INTO test_trigger_des_tbl VALUES(NEW.id1, NEW.id2, NEW.id3);
RETURN NEW;
END
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER insert_trigger
BEFORE INSERT ON test_trigger_src_tbl
FOR EACH ROW
EXECUTE PROCEDURE tri_insert_func();

INSERT INTO test_trigger_src_tbl VALUES(100,200,300);

SELECT * FROM test_trigger_src_tbl;
SELECT * FROM test_trigger_des_tbl;

CREATE OR REPLACE FUNCTION tri_update_func() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
UPDATE test_trigger_des_tbl SET id3 = NEW.id3 WHERE id1=OLD.id1;
RETURN OLD;
END
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER update_trigger
AFTER UPDATE ON test_trigger_src_tbl
FOR EACH ROW
EXECUTE PROCEDURE tri_update_func();

UPDATE test_trigger_src_tbl SET id3=400 WHERE id1=100;

SELECT * FROM test_trigger_src_tbl;
SELECT * FROM test_trigger_des_tbl;

CREATE OR REPLACE FUNCTION TRI_DELETE_FUNC() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
DELETE FROM test_trigger_des_tbl WHERE id1=OLD.id1;
RETURN OLD;
END
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER delete_trigger
BEFORE DELETE ON test_trigger_src_tbl
FOR EACH ROW
EXECUTE PROCEDURE tri_delete_func();

DELETE FROM test_trigger_src_tbl WHERE id1=100;

SELECT * FROM test_trigger_src_tbl;
SELECT * FROM test_trigger_des_tbl;  

ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER insert_trigger;
INSERT INTO test_trigger_src_tbl VALUES(100,200,300);
SELECT * FROM test_trigger_src_tbl;
SELECT * FROM test_trigger_des_tbl;
ALTER TABLE test_trigger_src_tbl DISABLE TRIGGER ALL;
DROP TRIGGER update_trigger ON test_trigger_src_tbl;
DROP TRIGGER delete_trigger_renamed ON test_trigger_src_tbl;      
复制

2.作业

CREATE TABLE A(id int);
CREATE TABLE B(id int);

CREATE OR REPLACE FUNCTION tri_insert() RETURNS TRIGGER AS
$$
DECLARE
BEGIN
INSERT INTO B VALUES (NEW.id), (NEW.id);
RETURN NEW;
END
$$ LANGUAGE PLPGSQL;

CREATE TRIGGER insert_trigger BEFORE INSERT ON A
FOR EACH ROW
EXECUTE PROCEDURE tri_insert();

INSERT INTO A VALUES (1);
SELECT * FROM B;

SELECT * FROM PG_TRIGGER;

ALTER TABLE A DISABLE TRIGGER ALL;
ALTER TRIGGER insert_trigger ON A RENAME TO ins_tri;
DROP TRIGGER ins_tri ON A;
复制

复制