Prev: Sleep command in db2
Next: How do I turn 2 separate select queries into the columns of 1 select query
From: Oliver on 23 Apr 2008 15:25
I'm fairly new to DB2.

I have been assigned to build a delete trigger that finds the data
type of each of the table's fields so that the trigger can then build
a string consisting of OLD values pre-wrapped in quote marks as
needed. The deleted record's field values, all strung together as a
single string, would then be inserted into a single archiving table
(an architecture I inherited and cannot change).

I've got the trigger doing what I want, except for the last part where
I want it to execute the insert statement. I can't even get it to run
something simple like this, where test_table has three fields.

create trigger trd_test_table
after delete on test_table
referencing old as o
for each row
mode db2sql
begin
execute immediate 'insert into test_table (1961, ''blackhawks'',
''stanley cup champions'')';
end;

When I try to use EXECUTE IMMEDIATE from a _stored procedure_, it
works fine. I'm hoping that I'm missing some syntax or some basic
concept. Or is it that triggers aren't allowed to do EXECUTE
IMMEDIATE?

Anyone have any pointers on the overall goal?

From: Lennart on 23 Apr 2008 16:06
On Apr 23, 9:25 pm, Oliver <JOHollo...(a)gmail.com> wrote:
> I'm fairly new to DB2.
>
> I have been assigned to build a delete trigger that finds the data
> type of each of the table's fields so that the trigger can then build
> a string consisting of OLD values pre-wrapped in quote marks as
> needed. The deleted record's field values, all strung together as a
> single string, would then be inserted into a single archiving table
> (an architecture I inherited and cannot change).
>
> I've got the trigger doing what I want, except for the last part where
> I want it to execute the insert statement. I can't even get it to run
> something simple like this, where test_table has three fields.
>
> create trigger trd_test_table
> after delete on test_table
> referencing old as o
> for each row
> mode db2sql
> begin
> execute immediate 'insert into test_table (1961, ''blackhawks'',
> ''stanley cup champions'')';
> end;
>
> When I try to use EXECUTE IMMEDIATE from a _stored procedure_, it
> works fine. I'm hoping that I'm missing some syntax or some basic
> concept. Or is it that triggers aren't allowed to do EXECUTE
> IMMEDIATE?
>
> Anyone have any pointers on the overall goal?

I don't understand your req's, so I don't understand why you would
need execute immediate. Since you have a trigger for each table that
you would like to audit(?), you know what columns you must handle.
Wouldn't something like the following do?


db2 -v -td@ -f aa.sql
drop table test_table
DB20000I The SQL command completed successfully.

create table test_table ( a int, b varchar(30), c varchar(30) )
DB20000I The SQL command completed successfully.

insert into test_table values (1,'jadajada','jadajada')
DB20000I The SQL command completed successfully.

drop table archive
DB20000I The SQL command completed successfully.

create table archive ( s varchar(300) )
DB20000I The SQL command completed successfully.

drop trigger trd_test_table
DB20000I The SQL command completed successfully.

create trigger trd_test_table
after delete on test_table
referencing old as o
for each row mode db2sql
begin atomic
declare s varchar(100);
set s = rtrim(char(o.a)) || rtrim(o.b) || rtrim(o.c);
insert into archive (s) values (s);
end
DB20000I The SQL command completed successfully.

delete from test_table
DB20000I The SQL command completed successfully.

select * from archive

S
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1jadajadajadajada

1 record(s) selected.



I actually don't know whether you can do execute immediate from a
trigger, but you should be able to call a proc from a trigger.

If the problem is that you don't want to write the triggers by hand, I
would suggest that you write a util in your favorite scripting
language that creates the triggers for you


/Lennart
From: Oliver on 23 Apr 2008 16:21
On Apr 23, 4:06 pm, Lennart <Erik.Lennart.Jons...(a)gmail.com> wrote:
> On Apr 23, 9:25 pm, Oliver <JOHollo...(a)gmail.com> wrote:
>
>
>
> > I'm fairly new to DB2.
>
> > I have been assigned to build a delete trigger that finds the data
> > type of each of the table's fields so that the trigger can then build
> > a string consisting of OLD values pre-wrapped in quote marks as
> > needed. The deleted record's field values, all strung together as a
> > single string, would then be inserted into a single archiving table
> > (an architecture I inherited and cannot change).
>
> > I've got the trigger doing what I want, except for the last part where
> > I want it to execute the insert statement. I can't even get it to run
> > something simple like this, where test_table has three fields.
>
> > create trigger trd_test_table
> > after delete on test_table
> > referencing old as o
> > for each row
> > mode db2sql
> > begin
> > execute immediate 'insert into test_table (1961, ''blackhawks'',
> > ''stanley cup champions'')';
> > end;
>
> > When I try to use EXECUTE IMMEDIATE from a _stored procedure_, it
> > works fine. I'm hoping that I'm missing some syntax or some basic
> > concept. Or is it that triggers aren't allowed to do EXECUTE
> > IMMEDIATE?
>
> > Anyone have any pointers on the overall goal?
>
> I don't understand your req's, so I don't understand why you would
> need execute immediate. Since you have a trigger for each table that
> you would like to audit(?), you know what columns you must handle.
> Wouldn't something like the following do?
>
> db2 -v -td@ -f aa.sql
> drop table test_table
> DB20000I The SQL command completed successfully.
>
> create table test_table ( a int, b varchar(30), c varchar(30) )
> DB20000I The SQL command completed successfully.
>
> insert into test_table values (1,'jadajada','jadajada')
> DB20000I The SQL command completed successfully.
>
> drop table archive
> DB20000I The SQL command completed successfully.
>
> create table archive ( s varchar(300) )
> DB20000I The SQL command completed successfully.
>
> drop trigger trd_test_table
> DB20000I The SQL command completed successfully.
>
> create trigger trd_test_table
> after delete on test_table
> referencing old as o
> for each row mode db2sql
> begin atomic
> declare s varchar(100);
> set s = rtrim(char(o.a)) || rtrim(o.b) || rtrim(o.c);
> insert into archive (s) values (s);
> end
> DB20000I The SQL command completed successfully.
>
> delete from test_table
> DB20000I The SQL command completed successfully.
>
> select * from archive
>
> S
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> 1jadajadajadajada
>
> 1 record(s) selected.
>
> I actually don't know whether you can do execute immediate from a
> trigger, but you should be able to call a proc from a trigger.
>
> If the problem is that you don't want to write the triggers by hand, I
> would suggest that you write a util in your favorite scripting
> language that creates the triggers for you
>
> /Lennart

Exactly, we don't want to maintain the triggers by hand, that's
exactly the point. That way, when changes occur to the table
structure, the associated trigger will still work without further
maintenance.
From: Serge Rielau on 23 Apr 2008 18:29
Oliver wrote:
> On Apr 23, 4:06 pm, Lennart <Erik.Lennart.Jons...(a)gmail.com> wrote:
>> On Apr 23, 9:25 pm, Oliver <JOHollo...(a)gmail.com> wrote:
>>
>>
>>
>>> I'm fairly new to DB2.
>>> I have been assigned to build a delete trigger that finds the data
>>> type of each of the table's fields so that the trigger can then build
>>> a string consisting of OLD values pre-wrapped in quote marks as
>>> needed. The deleted record's field values, all strung together as a
>>> single string, would then be inserted into a single archiving table
>>> (an architecture I inherited and cannot change).
>>> I've got the trigger doing what I want, except for the last part where
>>> I want it to execute the insert statement. I can't even get it to run
>>> something simple like this, where test_table has three fields.
>>> create trigger trd_test_table
>>> after delete on test_table
>>> referencing old as o
>>> for each row
>>> mode db2sql
>>> begin
>>> execute immediate 'insert into test_table (1961, ''blackhawks'',
>>> ''stanley cup champions'')';
>>> end;
>>> When I try to use EXECUTE IMMEDIATE from a _stored procedure_, it
>>> works fine. I'm hoping that I'm missing some syntax or some basic
>>> concept. Or is it that triggers aren't allowed to do EXECUTE
>>> IMMEDIATE?
>>> Anyone have any pointers on the overall goal?
>> I don't understand your req's, so I don't understand why you would
>> need execute immediate. Since you have a trigger for each table that
>> you would like to audit(?), you know what columns you must handle.
>> Wouldn't something like the following do?
>>
>> db2 -v -td@ -f aa.sql
>> drop table test_table
>> DB20000I The SQL command completed successfully.
>>
>> create table test_table ( a int, b varchar(30), c varchar(30) )
>> DB20000I The SQL command completed successfully.
>>
>> insert into test_table values (1,'jadajada','jadajada')
>> DB20000I The SQL command completed successfully.
>>
>> drop table archive
>> DB20000I The SQL command completed successfully.
>>
>> create table archive ( s varchar(300) )
>> DB20000I The SQL command completed successfully.
>>
>> drop trigger trd_test_table
>> DB20000I The SQL command completed successfully.
>>
>> create trigger trd_test_table
>> after delete on test_table
>> referencing old as o
>> for each row mode db2sql
>> begin atomic
>> declare s varchar(100);
>> set s = rtrim(char(o.a)) || rtrim(o.b) || rtrim(o.c);
>> insert into archive (s) values (s);
>> end
>> DB20000I The SQL command completed successfully.
>>
>> delete from test_table
>> DB20000I The SQL command completed successfully.
>>
>> select * from archive
>>
>> S
>> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>> 1jadajadajadajada
>>
>> 1 record(s) selected.
>>
>> I actually don't know whether you can do execute immediate from a
>> trigger, but you should be able to call a proc from a trigger.
>>
>> If the problem is that you don't want to write the triggers by hand, I
>> would suggest that you write a util in your favorite scripting
>> language that creates the triggers for you
>>
>> /Lennart
>
> Exactly, we don't want to maintain the triggers by hand, that's
> exactly the point. That way, when changes occur to the table
> structure, the associated trigger will still work without further
> maintenance.
Well, conveninec and speed do tend to oppose each other.
Anyway Lennard told you the solution: CALL

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
From: Lennart on 23 Apr 2008 23:33
On Apr 23, 10:21 pm, Oliver <JOHollo...(a)gmail.com> wrote:
> On Apr 23, 4:06 pm, Lennart <Erik.Lennart.Jons...(a)gmail.com> wrote:
>
>
>
> > On Apr 23, 9:25 pm, Oliver <JOHollo...(a)gmail.com> wrote:
>
> > > I'm fairly new to DB2.
>
> > > I have been assigned to build a delete trigger that finds the data
> > > type of each of the table's fields so that the trigger can then build
> > > a string consisting of OLD values pre-wrapped in quote marks as
> > > needed. The deleted record's field values, all strung together as a
> > > single string, would then be inserted into a single archiving table
> > > (an architecture I inherited and cannot change).
>
> > > I've got the trigger doing what I want, except for the last part where
> > > I want it to execute the insert statement. I can't even get it to run
> > > something simple like this, where test_table has three fields.
>
> > > create trigger trd_test_table
> > > after delete on test_table
> > > referencing old as o
> > > for each row
> > > mode db2sql
> > > begin
> > > execute immediate 'insert into test_table (1961, ''blackhawks'',
> > > ''stanley cup champions'')';
> > > end;
>
> > > When I try to use EXECUTE IMMEDIATE from a _stored procedure_, it
> > > works fine. I'm hoping that I'm missing some syntax or some basic
> > > concept. Or is it that triggers aren't allowed to do EXECUTE
> > > IMMEDIATE?
>
> > > Anyone have any pointers on the overall goal?
>
> > I don't understand your req's, so I don't understand why you would
> > need execute immediate. Since you have a trigger for each table that
> > you would like to audit(?), you know what columns you must handle.
> > Wouldn't something like the following do?
>
> > db2 -v -td@ -f aa.sql
> > drop table test_table
> > DB20000I The SQL command completed successfully.
>
> > create table test_table ( a int, b varchar(30), c varchar(30) )
> > DB20000I The SQL command completed successfully.
>
> > insert into test_table values (1,'jadajada','jadajada')
> > DB20000I The SQL command completed successfully.
>
> > drop table archive
> > DB20000I The SQL command completed successfully.
>
> > create table archive ( s varchar(300) )
> > DB20000I The SQL command completed successfully.
>
> > drop trigger trd_test_table
> > DB20000I The SQL command completed successfully.
>
> > create trigger trd_test_table
> > after delete on test_table
> > referencing old as o
> > for each row mode db2sql
> > begin atomic
> > declare s varchar(100);
> > set s = rtrim(char(o.a)) || rtrim(o.b) || rtrim(o.c);
> > insert into archive (s) values (s);
> > end
> > DB20000I The SQL command completed successfully.
>
> > delete from test_table
> > DB20000I The SQL command completed successfully.
>
> > select * from archive
>
> > S
> > ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> > 1jadajadajadajada
>
> > 1 record(s) selected.
>
> > I actually don't know whether you can do execute immediate from a
> > trigger, but you should be able to call a proc from a trigger.
>
> > If the problem is that you don't want to write the triggers by hand, I
> > would suggest that you write a util in your favorite scripting
> > language that creates the triggers for you
>
> > /Lennart
>
> Exactly, we don't want to maintain the triggers by hand, that's
> exactly the point. That way, when changes occur to the table
> structure, the associated trigger will still work without further
> maintenance.

I see, IMO it is better to generate static triggers during development
via some automatic script. A silly example:

#!/usr/bin/python

import DB2
import sys

dbname = sys.argv[1]
dbuser = sys.argv[2]
dbpass = sys.argv[3]
schema = sys.argv[4]
table = sys.argv[5]

conn = DB2.connect(dsn=dbname, uid=dbuser, pwd=dbpass)
c1 = conn.cursor()

template = """
create trigger %s.trd_%s
after delete on %s.%s
referencing old as o
for each row mode db2sql
begin atomic
insert into archive (s) values (%s);
end;"""

get_columns = """
select colname, typename from syscat.columns
where (tabschema, tabname) = (?, ?)
order by colno"""

columns = ''
c1.execute(get_columns, (schema, table))
for row in c1.fetchall():
name = row[0]
type = row[1]
# FIXME: handle type
columns += " o.%s ||" % (name)
columns = columns[:-2]

print template % (schema, table, schema, table, columns)


and then execute as in:

[db2inst1(a)wb-05 /tmp]$ ./generate_triggers.py LELLE db2inst1 ******
DB2INST1 TEST_TABLE

create trigger DB2INST1.trd_TEST_TABLE
after delete on DB2INST1.TEST_TABLE
referencing old as o
for each row mode db2sql
begin atomic
insert into archive (s) values ( o.A || o.B || o.C );
end;

I assume you have a list of tables that you want to audit. Unless
there are milions of them it will only take a second or two to
regenerate the trigger code.
 |  Next  |  Last
Pages: 1 2
Prev: Sleep command in db2
Next: How do I turn 2 separate select queries into the columns of 1 select query