--
-- Timestamp each row on insert and update
--

--
-- The table
--
create table any_table (
	col	int,
	col2	int,
	mod_date timestamp DEFAULT now()
);

create table other_table (
	othercol	int,
	othercol2	int,
	mod_date timestamp DEFAULT now()
);

insert into any_table (col, col2) values (1,1);
insert into any_table (col, col2) values (2,2);
insert into any_table (col, col2) values (3,3);
insert into other_table (othercol, othercol2) values (4,4);
insert into other_table (othercol, othercol2) values (5,5);
insert into other_table (othercol, othercol2) values (6,6);

--
-- Show default of now() works for inserts
--
select * from any_table;
select * from other_table;

--
-- Create a trigger function to do update the timestamp field
--
create or replace function ts_row()
returns TRIGGER as
'
BEGIN
   NEW.mod_date = now();
	RETURN NEW;
END;
' language 'plpgsql';

--
-- Attach the trigger function to the insert and update
-- of each table
--
create trigger upd_any_table BEFORE UPDATE
	ON any_table for each row
	EXECUTE PROCEDURE ts_row();

create trigger upd_other_table BEFORE UPDATE
	ON other_table for each row
	EXECUTE PROCEDURE ts_row();


--
-- Show update trigger is working
--
update any_table set col2=col2*2 where col2 = 2;
select * from any_table;

update other_table set othercol2=othercol2*2 where othercol2 = 5;
select * from any_table;