Обсуждение: Trigger
Could someone send me a quick example of a trigger.
Craig May wrote: > > Could someone send me a quick example of a trigger. Hope this helps. Chris Ryan <<< Clip below and execute to create example >>> -- -- FUNCTION: trigger_last_updated -- -- DESCRIPTION: -- This is a function called by the table triggers to update the last_updated -- field on insert and updates. -- create function trigger_last_updated() returns opaque as 'begin new.last_updated := now(); returnnew; end;' language 'plpgsql'; -- -- TABLE: test_tbl -- -- DESCRIPTION: -- A simple table to test my trigger -- create table test_tbl ( some_field varchar(10), last_updated timestamp not null default now() ); -- -- TRIGGER: trigger_insert_update_test_tbl -- -- DESCRIPTION: -- This is the trigger called on insert and updates of all the table that -- has the last_updated field. It will use the function trigger_last_updated -- The cool thing here is the function doesn't make specific reference to the -- table so you could create a different trigger for each table with the field -- last_updated and use the same function. -- create trigger trigger_insert_update_test_tbl before insert or update on test_tbl for each row execute procedure trigger_last_updated();
Chris Ryan wrote:
>
> Craig May wrote:
> >
> > Could someone send me a quick example of a trigger.
>
> Hope this helps.
>
> Chris Ryan
>
-- snipped code --
I am so sorry but you may have noticed my email client wrapped lines it
shouldn't have. I have attached the file this time.
Chris Ryan--
-- FILE: trigger_example.sql
--
-- DESCRIPTION:
-- This file shows the basics of creating a table with a trigger
--
-- Chris Ryan <chris@greatbridge.com> 09/06/2000
--
-- GENERAL DISCLAIMER:
-- Please feel free to use this in any way you see fit to copy, modify,
-- redistribute, etc.. I provide not warranty of the code nor may I be held
-- responsible for it's use/misuse should something bad happen including
-- intentional or acts of god.
--
--
-- FUNCTION: trigger_last_updated
--
-- DESCRIPTION:
-- This is a function called by the table triggers to update the last_updated
-- field on insert and updates.
--
create function trigger_last_updated()
returns opaque
as 'begin
new.last_updated := now();
return new;
end;'
language 'plpgsql';
--
-- TABLE: test_tbl
--
-- DESCRIPTION:
-- A simple table to test my trigger
--
create table test_tbl (
some_field varchar(10),
last_updated timestamp not null default now()
);
--
-- TRIGGER: trigger_insert_update_test_tbl
--
-- DESCRIPTION:
-- This is the trigger called on insert and updates of all the table that
-- has the last_updated field. It will use the function trigger_last_updated
-- The cool thing here is the function doesn't make specific reference to the
-- table so you could create a different trigger for each table with the field
-- last_updated and use the same function.
--
create trigger trigger_insert_update_test_tbl
before insert or update on test_tbl
for each row execute procedure trigger_last_updated();
There is a sample in postgres documentation. (See below).
the only problem is for using langage plpgsql.
If it is not understand by your database, you must use command
createlang plpgsql dbname
as the owner of the database.
CREATE TABLE emp ( empname text, salary int4, last_date datetime, last_user name);
CREATE FUNCTION emp_stamp () RETURNS OPAQUE AS BEGIN -- Check that empname and salary are given
IF NEW.empname ISNULL THEN RAISE EXCEPTION ''empname cannot be NULL value''; END IF;
IF NEW.salary ISNULL THEN RAISE EXCEPTION ''% cannot have NULL salary'', NEW.empname; END
IF;
-- Who works for us when she must pay for? IF NEW.salary < 0 THEN RAISE EXCEPTION
''%cannot have a negative salary'',
NEW.empname; END IF;
-- Remember who changed the payroll when NEW.last_date := ''now''; NEW.last_user :=
getpgusername(); RETURN NEW; END; ' LANGUAGE 'plpgsql';
CREATE TRIGGER emp_stamp BEFORE INSERT OR UPDATE ON emp FOR EACH ROW EXECUTE PROCEDURE emp_stamp();
----- Original Message -----
From: Craig May <craig.may@s2.enthdimension.com.au>
To: <pgsql-sql@postgresql.org>
Sent: Wednesday, September 06, 2000 10:27 PM
Subject: [SQL] Trigger
>
> Could someone send me a quick example of a trigger.
>
Hi, How do I get a row count, like "Select [COUNT] from Table" ?? Regards, Craig May Enth Dimension http://www.enthdimension.com.au
Select count(*) from Table At 04:58 AM 10/20/00 +0000, Craig May wrote: >Hi, > >How do I get a row count, like "Select [COUNT] from Table" ?? > >Regards, >Craig May > >Enth Dimension >http://www.enthdimension.com.au > >
Hello, You will need to do "SELECT count(attribute) FROM table;" or SELECT count(table.attribute);" At 04:58 AM 10/20/00 +0000, Craig May wrote: >Hi, > >How do I get a row count, like "Select [COUNT] from Table" ?? > >Regards, >Craig May > >Enth Dimension >http://www.enthdimension.com.au
Hi, there, You want how many rows in your table??? select count(*) from yourtablename; Craig May wrote: > Hi, > > How do I get a row count, like "Select [COUNT] from Table" ?? > > Regards, > Craig May > > Enth Dimension > http://www.enthdimension.com.au -- Jie LIANG Internet Products Inc. 10350 Science Center Drive Suite 100, San Diego, CA 92121 Office:(858)320-4873 jliang@ipinc.com www.ipinc.com
>>>>> "Brian" == Brian C Doyle <bcdoyle@mindspring.com> writes: Brian> Hello, You will need to do "SELECT count(attribute) FROM Brian> table;" or SELECT count(table.attribute);" You need to watch this: acspg=# create table tst ( acspg(# a integer acspg(# ); CREATE acspg=# insert into tst values (0); INSERT 333481 1 acspg=# insert into tst values (null); INSERT 333482 1 acspg=# insert into tst values (2); INSERT 333483 1 acspg=# select count(*) from tst;count ------- 3 (1 row) acspg=# select count(a) from tst;count ------- 2 (1 row) acspg=# select count(1) from tst;count ------- 3 (1 row) acspg=# If you use the attribut name, null values won't be counted. -Dan
On Fri, 20 Oct 2000, Craig May wrote: > How do I get a row count, like "Select [COUNT] from Table" ?? SELECT COUNT(*) FROM <table> <where clause> Brett W. McCoy http://www.chapelperilous.net --------------------------------------------------------------------------- Man's reach must exceed his grasp, for why else the heavens?