triggers.out

postgresql8.3.4源码,开源数据库

drop table tttest;
drop sequence ttdummy_seq;
--
-- tests for per-statement triggers
--
CREATE TABLE log_table (tstamp timestamp default timeofday()::timestamp);
CREATE TABLE main_table (a int, b int);
COPY main_table (a,b) FROM stdin;
CREATE FUNCTION trigger_func() RETURNS trigger LANGUAGE plpgsql AS '
BEGIN
	RAISE NOTICE ''trigger_func() called: action = %, when = %, level = %'', TG_OP, TG_WHEN, TG_LEVEL;
	RETURN NULL;
END;';
CREATE TRIGGER before_ins_stmt_trig BEFORE INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
CREATE TRIGGER after_ins_stmt_trig AFTER INSERT ON main_table
FOR EACH STATEMENT EXECUTE PROCEDURE trigger_func();
--
-- if neither 'FOR EACH ROW' nor 'FOR EACH STATEMENT' was specified,
-- CREATE TRIGGER should default to 'FOR EACH STATEMENT'
--
CREATE TRIGGER before_upd_stmt_trig AFTER UPDATE ON main_table
EXECUTE PROCEDURE trigger_func();
CREATE TRIGGER before_upd_row_trig AFTER UPDATE ON main_table
FOR EACH ROW EXECUTE PROCEDURE trigger_func();
INSERT INTO main_table DEFAULT VALUES;
NOTICE:  trigger_func() called: action = INSERT, when = BEFORE, level = STATEMENT
NOTICE:  trigger_func() called: action = INSERT, when = AFTER, level = STATEMENT
UPDATE main_table SET a = a + 1 WHERE b < 30;
NOTICE:  trigger_func() called: action = UPDATE, when = AFTER, level = ROW
NOTICE:  trigger_func() called: action = UPDATE, when = AFTER, level = ROW
NOTICE:  trigger_func() called: action = UPDATE, when = AFTER, level = ROW
NOTICE:  trigger_func() called: action = UPDATE, when = AFTER, level = ROW
NOTICE:  trigger_func() called: action = UPDATE, when = AFTER, level = STATEMENT
-- UPDATE that effects zero rows should still call per-statement trigger
UPDATE main_table SET a = a + 2 WHERE b > 100;
NOTICE:  trigger_func() called: action = UPDATE, when = AFTER, level = STATEMENT
-- COPY should fire per-row and per-statement INSERT triggers
COPY main_table (a, b) FROM stdin;
NOTICE:  trigger_func() called: action = INSERT, when = BEFORE, level = STATEMENT
NOTICE:  trigger_func() called: action = INSERT, when = AFTER, level = STATEMENT
SELECT * FROM main_table ORDER BY a, b;
 a  | b  
----+----
  6 | 10
 21 | 20
 30 | 40
 31 | 10
 50 | 35
 50 | 60
 81 | 15
    |   
(8 rows)

-- Test enable/disable triggers
create table trigtest (i serial primary key);
NOTICE:  CREATE TABLE will create implicit sequence "trigtest_i_seq" for serial column "trigtest.i"
NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit index "trigtest_pkey" for table "trigtest"
-- test that disabling RI triggers works
create table trigtest2 (i int references trigtest(i) on delete cascade);
create function trigtest() returns trigger as $$
begin
	raise notice '% % % %', TG_RELNAME, TG_OP, TG_WHEN, TG_LEVEL;
	return new;
end;$$ language plpgsql;
create trigger trigtest_b_row_tg before insert or update or delete on trigtest
for each row execute procedure trigtest();
create trigger trigtest_a_row_tg after insert or update or delete on trigtest
for each row execute procedure trigtest();
create trigger trigtest_b_stmt_tg before insert or update or delete on trigtest
for each statement execute procedure trigtest();
create trigger trigtest_a_stmt_tg after insert or update or delete on trigtest
for each statement execute procedure trigtest();
insert into trigtest default values;
NOTICE:  trigtest INSERT BEFORE STATEMENT
NOTICE:  trigtest INSERT BEFORE ROW
NOTICE:  trigtest INSERT AFTER ROW
NOTICE:  trigtest INSERT AFTER STATEMENT
alter table trigtest disable trigger trigtest_b_row_tg;
insert into trigtest default values;
NOTICE:  trigtest INSERT BEFORE STATEMENT
NOTICE:  trigtest INSERT AFTER ROW
NOTICE:  trigtest INSERT AFTER STATEMENT
alter table trigtest disable trigger user;
insert into trigtest default values;
alter table trigtest enable trigger trigtest_a_stmt_tg;
insert into trigtest default values;
NOTICE:  trigtest INSERT AFTER STATEMENT
insert into trigtest2 values(1);
insert into trigtest2 values(2);
delete from trigtest where i=2;
NOTICE:  trigtest DELETE AFTER STATEMENT
select * from trigtest2;
 i 
---
 1
(1 row)

alter table trigtest disable trigger all;
delete from trigtest where i=1;
select * from trigtest2;
 i 
---
 1
(1 row)

-- ensure we still insert, even when all triggers are disabled
insert into trigtest default values;
select *  from trigtest;
 i 
---
 3
 4
 5
(3 rows)

drop table trigtest2;
drop table trigtest;
-- dump trigger data
CREATE TABLE trigger_test (
        i int,
        v varchar
);
CREATE OR REPLACE FUNCTION trigger_data()  RETURNS trigger 
LANGUAGE plpgsql AS $$

declare

	argstr text;
	relid text;

begin

	relid := TG_relid::regclass;

	-- plpgsql can't discover its trigger data in a hash like perl and python
	-- can, or by a sort of reflection like tcl can, 
	-- so we have to hard code the names.
	raise NOTICE 'TG_NAME: %', TG_name;
	raise NOTICE 'TG_WHEN: %', TG_when;
	raise NOTICE 'TG_LEVEL: %', TG_level;
	raise NOTICE 'TG_OP: %', TG_op;
	raise NOTICE 'TG_RELID::regclass: %', relid;
	raise NOTICE 'TG_RELNAME: %', TG_relname;
	raise NOTICE 'TG_TABLE_NAME: %', TG_table_name;
	raise NOTICE 'TG_TABLE_SCHEMA: %', TG_table_schema;
	raise NOTICE 'TG_NARGS: %', TG_nargs;

	argstr := '[';
	for i in 0 .. TG_nargs - 1 loop
		if i > 0 then
			argstr := argstr || ', ';
		end if;
		argstr := argstr || TG_argv[i];
	end loop;
	argstr := argstr || ']';
	raise NOTICE 'TG_ARGV: %', argstr;

	if TG_OP != 'INSERT' then
		raise NOTICE 'OLD: %', OLD;
	end if;

	if TG_OP != 'DELETE' then
		raise NOTICE 'NEW: %', NEW;
	end if;

	if TG_OP = 'DELETE' then
		return OLD;
	else
		return NEW;
	end if;

end;
$$;
CREATE TRIGGER show_trigger_data_trig 
BEFORE INSERT OR UPDATE OR DELETE ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE trigger_data(23,'skidoo');
insert into trigger_test values(1,'insert');
NOTICE:  TG_NAME: show_trigger_data_trig
NOTICE:  TG_WHEN: BEFORE
NOTICE:  TG_LEVEL: ROW
NOTICE:  TG_OP: INSERT
NOTICE:  TG_RELID::regclass: trigger_test
NOTICE:  TG_RELNAME: trigger_test
NOTICE:  TG_TABLE_NAME: trigger_test
NOTICE:  TG_TABLE_SCHEMA: public
NOTICE:  TG_NARGS: 2
NOTICE:  TG_ARGV: [23, skidoo]
NOTICE:  NEW: (1,insert)
update trigger_test set v = 'update' where i = 1;
NOTICE:  TG_NAME: show_trigger_data_trig
NOTICE:  TG_WHEN: BEFORE
NOTICE:  TG_LEVEL: ROW
NOTICE:  TG_OP: UPDATE
NOTICE:  TG_RELID::regclass: trigger_test
NOTICE:  TG_RELNAME: trigger_test
NOTICE:  TG_TABLE_NAME: trigger_test
NOTICE:  TG_TABLE_SCHEMA: public
NOTICE:  TG_NARGS: 2
NOTICE:  TG_ARGV: [23, skidoo]
NOTICE:  OLD: (1,insert)
NOTICE:  NEW: (1,update)
delete from trigger_test;
NOTICE:  TG_NAME: show_trigger_data_trig
NOTICE:  TG_WHEN: BEFORE
NOTICE:  TG_LEVEL: ROW
NOTICE:  TG_OP: DELETE
NOTICE:  TG_RELID::regclass: trigger_test
NOTICE:  TG_RELNAME: trigger_test
NOTICE:  TG_TABLE_NAME: trigger_test
NOTICE:  TG_TABLE_SCHEMA: public
NOTICE:  TG_NARGS: 2
NOTICE:  TG_ARGV: [23, skidoo]
NOTICE:  OLD: (1,update)
      
DROP TRIGGER show_trigger_data_trig on trigger_test;
      
DROP FUNCTION trigger_data();
DROP TABLE trigger_test;
--
-- Test use of row comparisons on OLD/NEW
--
CREATE TABLE trigger_test (f1 int, f2 text, f3 text);
-- this is the obvious (and wrong...) way to compare rows
CREATE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$
begin
	if row(old.*) = row(new.*) then
		raise notice 'row % not changed', new.f1;
	else
		raise notice 'row % changed', new.f1;
	end if;
	return new;
end$$;
CREATE TRIGGER t
BEFORE UPDATE ON trigger_test
FOR EACH ROW EXECUTE PROCEDURE mytrigger();
INSERT INTO trigger_test VALUES(1, 'foo', 'bar');
INSERT INTO trigger_test VALUES(2, 'baz', 'quux');
UPDATE trigger_test SET f3 = 'bar';
NOTICE:  row 1 not changed
NOTICE:  row 2 changed
UPDATE trigger_test SET f3 = NULL;
NOTICE:  row 1 changed
NOTICE:  row 2 changed
-- this demonstrates that the above isn't really working as desired:
UPDATE trigger_test SET f3 = NULL;
NOTICE:  row 1 changed
NOTICE:  row 2 changed
-- the right way when considering nulls is
CREATE OR REPLACE FUNCTION mytrigger() RETURNS trigger LANGUAGE plpgsql as $$
begin
	if row(old.*) is distinct from row(new.*) then
		raise notice 'row % changed', new.f1;
	else
		raise notice 'row % not changed', new.f1;
	end if;
	return new;
end$$;
UPDATE trigger_test SET f3 = 'bar';
NOTICE:  row 1 changed
NOTICE:  row 2 changed
UPDATE trigger_test SET f3 = NULL;
NOTICE:  row 1 changed
NOTICE:  row 2 changed
UPDATE trigger_test SET f3 = NULL;
NOTICE:  row 1 not changed
NOTICE:  row 2 not changed
DROP TABLE trigger_test;
DROP FUNCTION mytrigger();