|Subject||Re: [firebird-support] Re: How can I skip an Insert ?|
On Thu, 2005-08-11 at 15:17 +0000, yazbeckmona wrote:
> --- In email@example.com, "Adam" <s3057043@y...> wrote:This query is slow even when you are searching by the primary key?
> > --- In firstname.lastname@example.org, "yazbeckmona"
> > <yazbeckmona@y...> wrote:
> > > Hello,
> > >
> > > I would like to add something to my trigger "BeforeInsert" that
> > would
> > > ignore every line that is already in the database. How could I do
> > > that, I have absolutely no idea, I never worked with trigger
> > before! I
> > > need to NOT insert lines that is already in the database (of
> > course,
> > > because now I have some primary key duplicate error). (And I
> > > want to change my SQL query, because I tried already everything
> > it
> > > is terribly long.)
> > >
> > > Here's my trigger with a key generator:
> > > AS
> > > BEGIN
> > > if (NEW.idemployeur IS null) then
> > > NEW.idEmployeur = GEN_ID(G_EmployeuridEmployeurGen7, 1);
> > > END
> > >
> > > Thank you so much for any help!
> > Triggers do not appear to be what you want.
> > You seem a little confused about the role of the queries etc.
> > Firebird will raise an exception if your primary key is not unique.
> > In other words, you must be supplying a idEmployeur that is already
> > in the table (a duplicate), and that is against the whole concept
> > a primary key. Your application should catch the exception and
> > accordingly. It would be very confusing logic to have the database
> > quietly eat any duplicate data, you would not then know where your
> > record went. Instead, you now have a mechanism where the database
> > informs you that the query operation failed, and why it failed. Use
> > it to your advantage.
> > In fact, the way you would do input validation in the before insert
> > trigger would be to raise a custom exception to prevent the insert
> > from succeeding.
> > Adam
> The thing I am trying to do is that I have 2 tables (tab1 and tab2).
> I want to store the data form tab1 in tab2 IF tab2 doesn't already
> have it. It is so simple, I do this usually but now, the table are
> too large and when I write my query using something like
> INSERT id, c1, c2, c3 IN tab2 FROM tab1 WHERE NOT EXISTS (SELECT id
> FROM tab2 WHERE tab1.id = tab2.id) it is really terribly long.
>[Non-text portions of this message have been removed]
> So that's why I wanted to skip the insert instead. So How can I write
> a try and catch exception in the trigger? Hopping it will be a little
> faster than the query...
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
> Also search the knowledgebases at http://www.ibphoenix.com
> Yahoo! Groups Links