CREATE OR REPLACE TRIGGER trig1
BEFORE
INSERT OR
UPDATE OR
DELETE
ON tab1
BEGIN
CASE
WHEN INSERTING THEN
DBMS_OUTPUT.PUT_LINE('Inserting');
WHEN UPDATING THEN
DBMS_OUTPUT.PUT_LINE('Updating');
WHEN DELETING THEN
DBMS_OUTPUT.PUT_LINE('Deleting');
END CASE;
END;
/
set serveroutput on size 10000
insert into tab1 values(1);
update tab1 set col1 = 1;
delete from tab1;
commit;
set serveroutput off
show triggers\G
SHOW CREATE trigger trig1;
delimiter //
create trigger trig1 before insert on tab1 for each row
begin
insert into tab2(col1) values(new.col1);
end;
//
delimiter ;
select * from tab1;
select * from tab2;
insert into tab1 values(22);
SELECT * FROM pg_trigger;
CREATE OR REPLACE FUNCTION func2()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO tab4 VALUES (NEW.*);
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER trig1
before INSERT ON tab3
FOR EACH ROW EXECUTE PROCEDURE func2();
select * from tab3;
select * from tab4;
insert into tab3 values(11);
CREATE TABLE test_main_table
(
no INT
,name VARCHAR(20)
)
GO
CREATE TABLE test_sub_table
(
no INT
,name VARCHAR(20)
,trigger_kind INT
,trigger_date DATETIME
)
GO
CREATE TRIGGER test_main_insert ON test_main_table
FOR INSERT
AS
INSERT INTO test_sub_table
( no, name, trigger_kind, trigger_date )
SELECT no, name, 1, GETDATE() from inserted
GO
CREATE TRIGGER test_main_update ON test_main_table
FOR UPDATE
AS
INSERT INTO test_sub_table
( no, name, trigger_kind, trigger_date )
SELECT no, name, 2, GETDATE() from inserted
GO
CREATE TRIGGER test_main_DELETE ON test_main_table
FOR DELETE
AS
INSERT INTO test_sub_table
( no, name, trigger_kind, trigger_date )
SELECT no, name, 3, GETDATE() from deleted
GO
INSERT INTO test_main_table VALUES( 1, '太郎')
UPDATE test_main_table SET name = '次郎' WHERE no = 1
DELETE FROM test_main_table WHERE no = 1
go
SELECT no, name, trigger_kind, trigger_date FROM test_sub_table ORDER BY trigger_date
go
