Thread: trigger

Aftab Alam schrieb:
> I want to create a trigger in PostgresSQL
>
> In trigger, Before inserting the record, if data is already in the
> table, the trigger fire the mesaage that data is already there, and
> after that trigger ckeck for next insert statement.

You write a function that returns the type "trigger" and checks whether
the data is already there (i.e. by SELECTing INTO a variable the number
of records with your given values and checking if the variable is >0).
If it is, let it shout out your NOTICE and RETURN NULL; if it's not,
RETURN NEW;
Afterwards, you create the actual trigger BEFORE INSERT ON your table
FOR EACH ROW and let it execute your recently written function :-)

Good luck,

Stefan Balzter

Aftab Alam wrote:
> Hello,
>
> I want to create a trigger in PostgresSQL
>
> In trigger, Before inserting the record, if data is already in the table,
> the trigger fire the mesaage that data is already there, and after that
> trigger ckeck for next insert statement.

Aftab - can you explain in some more detail. Raising an error on an
insert will happen automatically if you violate a unique constraing.

What is this "next insert statment" you're talking about?

--
   Richard Huxton
   Archonet Ltd

Hi there,

 CREATE TABLE emp_mas
(
  emp_com_cd char(2) NOT NULL,
  emp_loc_cd numeric NOT NULL,
  emp_cd numeric NOT NULL,
  emp_sal numeric,
  emp_m_name varchar(50),
  emp_full_name varchar(50),
  emp_sex varchar(1)
)

delete from emp_mas where emp_com_cd  = 'KB' and emp_loc_cd  = 1 and emp_cd
in (1,2);
insert into emp_mas values ('KB', 1, 1,1,'aftab','aftab alam','M');
insert into emp_mas values ('KB', 1, 1,2,'amit','amit sharma','M');

in the above statement if aftab is already available in the database then
the trigger does not allowing to delete the data for the database if not
insert statement is going to fire.

wating for reply.

thanks & regrds,
aftab








-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org]On Behalf Of Richard Huxton
Sent: Friday, November 18, 2005 7:06 PM
To: aalam@tatashare.com
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] trigger


Aftab Alam wrote:
> Hello,
>
> I want to create a trigger in PostgresSQL
>
> In trigger, Before inserting the record, if data is already in the table,
> the trigger fire the mesaage that data is already there, and after that
> trigger ckeck for next insert statement.

Aftab - can you explain in some more detail. Raising an error on an
insert will happen automatically if you violate a unique constraing.

What is this "next insert statment" you're talking about?

--
   Richard Huxton
   Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly