file://Some Stuff because the Mailer doesn't like a first word of Set
-----Original Message-----
From: Tony Blomfield <[EMAIL PROTECTED]>
To: [EMAIL PROTECTED] <[EMAIL PROTECTED]>
Date: Wednesday, 2 February 2000 4:43 PM
Subject: Re: [DUG]: Interbase Error : What am I missing here??? (Interbase
Newbie)
>set term !! ;
>
>Drop Trigger DelProductGroup0 !!
>/**********************EXCEPTIONS******************************************
*
>*****/
>create exception EDelProductGroup "Cannot delete Product Group as it is in
>use by Product table" !!
>/*********************DELETE
>TRIGGERS*******************************************/
>
>
>create trigger DelProductGroup0 for ProductGroup
> before delete position 0 as
> declare variable NumRecs Integer;
>begin
> select Count(*) from Product where ProductGroupID = Old.ProductGroupID
>into Numrecs;
> if (NumRecs>0) then Exception EDelProductGroup;
>end !!
>
>
>set term !! ;
>
>
>Triggers are very good in IB.
>
>Simple Concepts you need to be aware of are:-
>
>New.ColumnName refers to the new value after an insert or update
>Old.ColumnName = Ditto
>
>Triggers can be Before or After eg BeforeUpdate, After Delete
>
>Write many triggers each with a discreet operation, You Cant combine all
>your triggers into one like you can in SQL Server.
>
>The trigger position determines the order in the list of triggers for that
>trigger type that it will be fired.
>
>Use triggers extensivly for all your Delete Cascade and Restrict Deletes.
>
>The example at top shows how to use an exception in a Restrict Delete
>trigger so the user gets back a nice friendly message.
>
>
>Thats about it. With a little practise, they become boringly easy to use.
If
>there are any products, then you cant delete a Product group is what the
>exception does for you here.
>
>Good luck,
>
>Tony.
>
>-----Original Message-----
>From: Donovan J. Edye <[EMAIL PROTECTED]>
>To: Multiple recipients of list delphi <[EMAIL PROTECTED]>
>Date: Wednesday, 2 February 2000 3:32 PM
>Subject: RE: [DUG]: Interbase Error : What am I missing here??? (Interbase
>Newbie)
>
>
>N,
>
>As I said. Newbie.... Generators are something I have not come across. (Ex
>MS-SQL Trying to see if what you saud about IB is true ;-)) Look cool
>though..... HOWEVER....
>
>Given
>
>SHOW TRIGGER SOMETABLEAFTERUPDATE
>
>Triggers on Table SOMETABLE:
>SOMETABLEAFTERUPDATE, Sequence: 0, Type: AFTER UPDATE, Active
>AS
>BEGIN
>EXECUTE PROCEDURE WRITETRANLOG;
>END
>
>Now the above trigger has an incorrect param list for WRITETRANLOG. So I
>figure I would drop the trigger and re-create it. But all I keep getting
>is....
>
>Statement failed, SQLCODE = -104
>invalid request BLR at offset 19
>-parameter mismatch for procedure WRITETRANLOG
>
>I KNOW THIS. That is why I am attempting to drop the trigger. Even
>attempting to set the trigger INACTIVE produces the same error. Is there
>another sequence that I should be doing this in??
>
>------------------------------------------------------------------------
>--Donovan
>Donovan J. Edye [www.edye.wattle.id.au]
>Namadgi Systems, Delphi Developer
>Web: www.namsys.com.au E-Mail: [EMAIL PROTECTED]
>Voice: +61 2 6285-3460 Fax: +61 2 6285-3459
>TVisualBasic = Class(None);
>------------------------------------------------------------------------
>
>
>> -----Original Message-----
>> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
>> Behalf Of Nic Wise
>> Sent: Wednesday, 2 February 2000 12:20
>> To: Multiple recipients of list delphi
>> Subject: Re: [DUG]: Interbase Error : What am I missing here???
>> (Interbase Newbie)
>>
>>
>> > G'Day All,
>> >
>> > Please put me out of my misery. Give the following stored
procedure.....
>>
>> Gimme a second, but first, and MOST importantly:
>>
>> > DECLARE VARIABLE NextTranID Integer;
>> > BEGIN
>> > SELECT MAX(TranID) FROM CAS INTO :NextTranID;
>> > NextTranID = NextTranID + 1;
>> > INSERT INTO CAS VALUES (:NextTranID, "Some Magical SQL");
>> > END
>>
>> This REALLY REALLY REALLY should be:
>>
>> CREATE GENERATOR GEN_TRANID;
>>
>>
>> create procedure writetotranlog( something varchar(255)) AS
>> DECLARE VARIABLE NextTranID Integer;
>> BEGIN
>> NextTranID = Gen_ID(GEN_TRANID,1);
>> INSERT INTO CAS VALUES (:NextTranID, :something);
>> END
>>
>>
>> Thats exactly the POINT of generators!!!!
>>
>> this works fine for me:
>>
>> make a generator:
>>
>> create generator tempgen;
>> (or make it in SQLExplorer)
>>
>> create the procedure testproc:
>>
>> CREATE PROCEDURE TESTPROC (
>> THING VARCHAR(255)
>> ) AS
>> declare variable myint integer;
>> BEGIN
>> myint = gen_id(tempgen,1);
>> insert into jobtype values (:thing || cast(:myint as varchar(255)));
>> END
>>
>> (I just concat (||) the thing I pass in with my integer, converted to a
>> string)
>>
>> And the trigger:
>>
>> CREATE TRIGGER PRIVILAGETRIGGER1 FOR PRIVILAGE AFTER INSERT POSITION 0 AS
>> BEGIN
>> execute procedure testproc("trigger");
>> END
>>
>>
>> Works for me. I get exactly the results I would expect from this.
>>
>> Nic.
>>
>>
>>
>>
>>
>>
>> ------------------------------------------------------------------
>> ---------
>> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
>> Website: http://www.delphi.org.nz
>>
>
>---------------------------------------------------------------------------
> New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
