Re: INSERT TRIGGER
On 4 Jun, 14:26, Stephan Kirmse <kir...@fh-brandenburg.de> wrote:
> Hello,
>
> once again today I´m searching for help :-/
>
> I need to create an INSERT-TRIGGER.
>
> CREATE TRIGGER t1> INSERT ON test
> REFERENCING OLD AS pre NEW AS post
> FOR EACH ROW WHEN (test.nr = 1)
> (INSERT INTO test2 VALUES (1));
>
> I want to add a row (with value=1) to the table test2 everytime a new row with nr=1 in table test is added.
>
> The error message tells me "Incorrect use of old or new values correlation name inside trigger."
>
> In the manual I read:
>
> [...] You cannot specify an old correlation name for an insert trigger. You cannot specify a new correlation name for a delete trigger. [...]
>
> But how can I make a condition an the "OLD" table ??
>
> Thanks for your patience
>
> Regards, Stephan
You have no "old" values, just "new" values from an insert.
create database tbp_insert in dbspace1 with buffered log;
create table tab1 (col1 int, col2 char(20));
create table tab2 (col1 int, col2 char(20));
create trigger insert_tab1insert on tab1
referencing new as post
for each row
when (post.col1 = 1)
(insert into tab2 values (1,"From Trigger"));
insert into tab1 values (1,"One");
insert into tab1 values (2,"Two");
insert into tab1 values (1,"Three");
select * from tab1;
select * from tab2;
Produces the following :
col1 col2
1 One
2 Two
1 Three
col1 col2
1 From Trigger
1 From Trigger
alright - it works ... thanks for the great response.
Let me add an additional question:
Is it possible to insert more than one SQL-Query in the Action-Part of the Trigger?
e.g.
CREATE TRIGGER t1INSERT ON test
REFERENCING NEW AS post
FOR EACH ROW WHEN (post.nr = 1)
(INSERT INTO test2 VALUES (1); INSERT INTO test3 VALUES (1););
Or do I have to write a procedure?
Thx, Stephan
TBP schrieb:
> On 4 Jun, 14:26, Stephan Kirmse <kir...@fh-brandenburg.de> wrote:
>> Hello,
>>
>> once again today I'm searching for help :-/
>>
>> I need to create an INSERT-TRIGGER.
>>
>> CREATE TRIGGER t1>> INSERT ON test
>> REFERENCING OLD AS pre NEW AS post
>> FOR EACH ROW WHEN (test.nr = 1)
>> (INSERT INTO test2 VALUES (1));
>>
>> I want to add a row (with value=1) to the table test2 everytime a new row with nr=1 in table test is added.
>>
>> The error message tells me "Incorrect use of old or new values correlation name inside trigger."
>>
>> In the manual I read:
>>
>> [...] You cannot specify an old correlation name for an insert trigger. You cannot specify a new correlation name for a delete trigger. [...]
>>
>> But how can I make a condition an the "OLD" table ??
>>
>> Thanks for your patience
>>
>> Regards, Stephan
>
> You have no "old" values, just "new" values from an insert.
>
> create database tbp_insert in dbspace1 with buffered log;>
> create table tab1 (col1 int, col2 char(20));>
> create table tab2 (col1 int, col2 char(20));>
> create trigger insert_tab1> insert on tab1
> referencing new as post
> for each row
> when (post.col1 = 1)
> (insert into tab2 values (1,"From Trigger"));
>
> insert into tab1 values (1,"One");
> insert into tab1 values (2,"Two");
> insert into tab1 values (1,"Three");>
> select * from tab1;
> select * from tab2;>
> Produces the following :
>
> col1 col2
>
> 1 One
> 2 Two
> 1 Three
>
>
> col1 col2
>
> 1 From Trigger
> 1 From Trigger
Stephan Kirmse wrote:
> alright - it works ... thanks for the great response.
>
> Let me add an additional question:
> Is it possible to insert more than one SQL-Query in the Action-Part of
> the Trigger?
>
> e.g.
>
> CREATE TRIGGER t1> INSERT ON test
> REFERENCING NEW AS post
> FOR EACH ROW WHEN (post.nr = 1)
> (INSERT INTO test2 VALUES (1); INSERT INTO test3 VALUES (1););
>
> Or do I have to write a procedure?
>
> Thx, Stephan
>
> TBP schrieb:
>
>> On 4 Jun, 14:26, Stephan Kirmse <kir...@fh-brandenburg.de> wrote:
>>
>>> Hello,
>>>
>>> once again today I'm searching for help :-/
>>>
>>> I need to create an INSERT-TRIGGER.
>>>
>>> CREATE TRIGGER t1>>> INSERT ON test
>>> REFERENCING OLD AS pre NEW AS post
>>> FOR EACH ROW WHEN (test.nr = 1)
>>> (INSERT INTO test2 VALUES (1));
>>>
>>> I want to add a row (with value=1) to the table test2 everytime a new
>>> row with nr=1 in table test is added.
>>>
>>> The error message tells me "Incorrect use of old or new values
>>> correlation name inside trigger."
>>>
>>> In the manual I read:
>>>
>>> [...] You cannot specify an old correlation name for an insert
>>> trigger. You cannot specify a new correlation name for a delete
>>> trigger. [...]
>>>
>>> But how can I make a condition an the "OLD" table ??
>>>
>>> Thanks for your patience
>>>
>>> Regards, Stephan
>>
>>
>> You have no "old" values, just "new" values from an insert.
>>
>> create database tbp_insert in dbspace1 with buffered log;>>
>> create table tab1 (col1 int, col2 char(20));>>
>> create table tab2 (col1 int, col2 char(20));>>
>> create trigger insert_tab1>> insert on tab1
>> referencing new as post
>> for each row
>> when (post.col1 = 1)
>> (insert into tab2 values (1,"From Trigger"));
>>
>> insert into tab1 values (1,"One");
>> insert into tab1 values (2,"Two");
>> insert into tab1 values (1,"Three");>>
>> select * from tab1;
>> select * from tab2;>>
>> Produces the following :
>>
>> col1 col2
>>
>> 1 One
>> 2 Two
>> 1 Three
>>
>>
>> col1 col2
>>
>> 1 From Trigger
>> 1 From Trigger
Hi,
Well, reading the fine manual we see the following :
For Version 10 :
No more than one Insert trigger, and no more than one Delete trigger, can be defined on one table.
For Version 11 :
Any number of Insert triggers, and any number of Delete trigger, can be defined on the same table.
So, you have a few possibilities :
1. Under Version 10 use a stored procedure.
2. Under Version 10 use a trigger on "tab1" to insert into "tab2"; use a trigger on "tab2" to insert into "tab3"
3. Under Version 11 have multiple insert triggers (or a stored procedure) :
create database tbp_insert in dbspace_1 with buffered log;
create table tab1 (col1 int, col2 char(30));
create table tab2 (col1 int, col2 char(30));
create table tab3 (col1 int, col2 char(30));
create trigger insert_tab1_1 insert on tab1referencing new as post
for each row when (post.col1 = 1) (insert into tab2 values (1,"From Trigger insert_tab1_1"));
create trigger insert_tab1_2 insert on tab1referencing new as post
for each row when (post.col1 = 1) (insert into tab3 values (1,"From Trigger insert_tab1_2"));
insert into tab1 values (1,"Insert one");
insert into tab1 values (2,"Insert two");
insert into tab1 values (1,"Insert three");select "tab1", * from tab1;
select "tab2", * from tab2;
select "tab3", * from tab3;
(constant) col1 col2
tab1 1 Insert one
tab1 2 Insert two
tab1 1 Insert three
3 row(s) retrieved.
(constant) col1 col2
tab2 1 From Trigger insert_tab1_1
tab2 1 From Trigger insert_tab1_1
2 row(s) retrieved.
(constant) col1 col2
tab3 1 From Trigger insert_tab1_2
tab3 1 From Trigger insert_tab1_2
2 row(s) retrieved.