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? 6 4438
On Apr 23, 9:25 pm, Oliver <JOHollo...@gma il.comwrote:
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
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1jadajadajadaja da
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
On Apr 23, 4:06 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
On Apr 23, 9:25 pm, Oliver <JOHollo...@gma il.comwrote:
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
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1jadajadajadaja da
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.
Oliver wrote:
On Apr 23, 4:06 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
>On Apr 23, 9:25 pm, Oliver <JOHollo...@gma il.comwrote:
>>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 ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 1jadajadajadaj ada
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
Lennart wrote:
On Apr 23, 10:21 pm, Oliver <JOHollo...@gma il.comwrote:
On Apr 23, 4:06 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
[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.
On Apr 24, 5:41 am, "Dave Hughes" <d...@waveform. plus.comwrote:
Lennart wrote:
On Apr 23, 10:21 pm, Oliver <JOHollo...@gma il.comwrote:
On Apr 23, 4:06 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
[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
On Apr 24, 7:01 am, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
On Apr 24, 5:41 am, "Dave Hughes" <d...@waveform. plus.comwrote:
Lennart wrote:
On Apr 23, 10:21 pm, Oliver <JOHollo...@gma il.comwrote:
On Apr 23, 4:06 pm, Lennart <Erik.Lennart.J ons...@gmail.co mwrote:
[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. This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: joy |
last post by:
I am writting a trigger, I have table parts(PNO,..,QOH,..), before
delete the PNO row, I need to check QOH. Part of the trigger code
like:
create or replace trigger parts_bef_del_row
before delete on parts
for each row
declare
num integer := 0;
|
by: Bill Smith |
last post by:
The initial row is inserted with the colPartNum column containing a valid
LIKE pattern, such as (without the single quotes) 'AB%DE'.
I want to update the column value with the results of a query against a
different table (that uses the LIKE predicate) but cannot get around the
SQL0132 error .
I have tried the hex notation after the LIKE such as (without the
quotes)...
" where colNewPartNum like ( X'27' || nnn.colPartNum || X'27) " ,...
|
by: Gustavo Randich |
last post by:
Hello,
I'm writing an automatic SQL parser and translator from Informix to
DB2. Now I'm faced with one of the most difficult things to translate,
the "foreach execute procedure" functionality provided by Informix.
This is the problem: the execution of the translated SQL leaves the
rows in the temp table correctly but raises error SQL0480N. It's very
simple to try it:
------------
|
by: JohnO |
last post by:
Hi Folks,
I have an update trigger that fails (it inserts an audit table record)
in some circumstances. This is causing the triggering transaction to
fail and roll back. Is there any way to prevent this? If the trigger
fails I still want the triggering transaction to continue.
Cheers,
JohnO
|
by: JohnO |
last post by:
Thanks to Serge and MarkB for recent tips and suggestions.
Ive rolled together a few stored procedures to assist with creating
audit triggers automagically. Hope someone finds this as useful as I've
found it educational.
Note:
- I build this for use in a JDEdwards OneWorld environment. I'm not
sure how generic others find it but it should be fairly generic.
- I use a C stored procedure GETJOBNAME to get some extra audit data,
| |
by: Reshmi Jacob |
last post by:
Hello,
Can any one help me in creating a trigger to update system date into a table while inserting a record into that table. I tried it like this, it is showing error !!!
The following error has occurred:
ORA-04091: table ACG.CENTREMST is mutating, trigger/function may not see it
ORA-06512: at "ACG.CENTREMST_INSERT", line 5
ORA-04088: error during execution of trigger 'ACG.CENTREMST_INSERT'
|
by: Reshmi Jacob |
last post by:
Hello,
Can any one help me in creating a trigger to update system date into a table while inserting a record into that table. I tried it like this, it is showing error !!!
The following error has occurred:
ORA-04091: table ACG.CENTREMST is mutating, trigger/function may not see it
ORA-06512: at "ACG.CENTREMST_INSERT", line 5
ORA-04088: error during execution of trigger 'ACG.CENTREMST_INSERT'
|
by: wugon.net |
last post by:
Hi ,
Anyone know how to monitor db2 trigger activity ?
We suffer some trigger issue , and we try to monitor trigger's
behavior use event monitor and db2audit, but both tools can not get
trigger's sql statment and cost, have other tools can get trigger's
executing sql statment and cost ?
our test case as follow:
Env:
|
by: wugon.net |
last post by:
Hi ,
Anyone know how to monitor db2 trigger activity ?
We suffer some trigger issue today and we try to monitor trigger's
behavior use event monitor and db2audit, but both tools can not get
trigger's sql statment and cost, have other tools can get trigger's
executing sql statment and cost ?
our test case as follow:
Env:
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |