can a trigger do an execute immediate?

[Oliver]

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?

[Lennart]

On Apr 23, 9:25 pm, Oliver <[e-mail]:

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

[Oliver]

On Apr 23, 4:06 pm, Lennart <[e-mail]:

On Apr 23, 9:25 pm, Oliver <[e-mail]:

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.

[Serge Rielau]

Oliver wrote:

On Apr 23, 4:06 pm, Lennart <[e-mail]:

>On Apr 23, 9:25 pm, Oliver <[e-mail]:

>>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

[Dave Hughes]

Lennart wrote:

On Apr 23, 10:21 pm, Oliver <[e-mail]:

On Apr 23, 4:06 pm, Lennart <[e-mail]:

[snip]

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:

[snip]

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.

This would certainly be my preference. Especially as "when changes
occur to the table structure" the associated triggers may get
invalidated and will need to be recreated anyway (depending on what
change occurred and how it was implemented).
Cheers,

Dave.

[Lennart]

On Apr 24, 5:41 am, "Dave Hughes" <[e-mail]:

Lennart wrote:

On Apr 23, 10:21 pm, Oliver <[e-mail]:

On Apr 23, 4:06 pm, Lennart <[e-mail]:

[snip]

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:

[snip]

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.

This would certainly be my preference. Especially as "when changes
occur to the table structure" the associated triggers may get
invalidated and will need to be recreated anyway (depending on what
change occurred and how it was implemented).

Exactly my thoughts too. As a matter of fact I do all my upgrades via
a utility that among other things verifies this before a version is
committed to the database. In case someone is interested I have an
ASSERT procedure defined as:

CREATE PROCEDURE TOOLBOX.ASSERT(stmt varchar(1000))
LANGUAGE SQL
BEGIN

DECLARE tmpstmt varchar(1100);
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
SIGNAL SQLSTATE '77000'
SET MESSAGE_TEXT = 'ASSERTION FAILED!';

A: BEGIN
-- Do nothing if drop session.tmp fails
DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
BEGIN
END;

DROP TABLE SESSION.TMP;
END;

DECLARE GLOBAL TEMPORARY TABLE SESSION.TMP (y int);

SET tmpstmt = 'insert into session.tmp ' || stmt;
execute immediate tmpstmt;
END @
COMMENT ON PROCEDURE TOOLBOX.ASSERT IS 'Raises exception if stmt
return 0 rows.
Stmt must be of form select <intfrom ... Note that sql string delim
must be quoted' @
Before the version is commited to the database, the following call is
always made:

call toolbox.assert
('select 1 from lateral(values 1) x where not exists (
select 1 from syscat.tables
where tabschema in (
<relevant tableschemas>
) and status <''N''
union all
select 1 from syscat.triggers
where trigschema in (
<relevant trigschemas>
) and valid <''Y''
)' ) @

This way I will be notified that I messed something up, and the
transaction is rolled back.
/Lennart

[Oliver]

On Apr 24, 7:01 am, Lennart <[e-mail]:

On Apr 24, 5:41 am, "Dave Hughes" <[e-mail]:

Lennart wrote:

On Apr 23, 10:21 pm, Oliver <[e-mail]:
On Apr 23, 4:06 pm, Lennart <[e-mail]:

[snip]

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:

[snip]

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.

This would certainly be my preference. Especially as "when changes
occur to the table structure" the associated triggers may get
invalidated and will need to be recreated anyway (depending on what
change occurred and how it was implemented).

Exactly my thoughts too. As a matter of fact I do all my upgrades via
a utility that among other things verifies this before a version is
committed to the database. In case someone is interested I have an
ASSERT procedure defined as:

CREATE PROCEDURE TOOLBOX.ASSERT(stmt varchar(1000))
LANGUAGE SQL
BEGIN

DECLARE tmpstmt varchar(1100);
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
SIGNAL SQLSTATE '77000'
SET MESSAGE_TEXT = 'ASSERTION FAILED!';

A: BEGIN
-- Do nothing if drop session.tmp fails
DECLARE CONTINUE HANDLER FOR SQLSTATE '42704'
BEGIN
END;

DROP TABLE SESSION.TMP;
END;

DECLARE GLOBAL TEMPORARY TABLE SESSION.TMP (y int);

SET tmpstmt = 'insert into session.tmp ' || stmt;
execute immediate tmpstmt;
END @

COMMENT ON PROCEDURE TOOLBOX.ASSERT IS 'Raises exception if stmt
return 0 rows.
Stmt must be of form select <intfrom ... Note that sql string delim
must be quoted' @

Before the version is commited to the database, the following call is
always made:

call toolbox.assert
('select 1 from lateral(values 1) x where not exists (
select 1 from syscat.tables
where tabschema in (
<relevant tableschemas>
) and status <''N''
union all
select 1 from syscat.triggers
where trigschema in (
<relevant trigschemas>
) and valid <''Y''
)' ) @

This way I will be notified that I messed something up, and the
transaction is rolled back.

/Lennart

I see your point about attended maintenance being the preferred
practice, as opposed to what I'm trying to do. Thanks for the insight.