Can use cursor in trigger?

[c***@talgentra.com]

Any idea whether cursor can be use within a trigger?
Thanks.

[Knut Stolze]

Post by c***@talgentra.com
Any idea whether cursor can be use within a trigger?

Not directly. You can use it within a stored procedure and call the
procedure from the trigger.

However: the real question is what you try to do. Very often you don't even
need a cursor.

[tonkuma]

I agree sicerely with Knut Stolze thar cursor is not needed many situation.
Many people thinks according with procedural algorithm including even at the time using SQL/PL.
But, if fully use relational algorithm, it is not neccesary almost every time to use procedural algorithm.
And many times, DB2 could produce more optimal execution plan by coding sorce SQL code acorrding with relational algorithm.

Note: This is my opnion based on my experience. So, others might have different opinions.

[c***@talgentra.com]

Hi Knut Stolze/tonkuma,

What I'm trying to achieve here is to build a generic trigger that can be plugged in to A table with any column name. The table is used to stored all kind of flags with ACCOUNTID as Primary Key. The requirement here is if Any flag in this table is updated with 'Y', then it will perform neccessary action.

The table structure will most likely look like this:-

ACCOUNTID DECIMAL(10,0),
FLAG1 VARCHAR(1),
FLAG2 VARCHAR(1),
FLAG3 VARCHAR(1),
FLAG4 VARCHAR(1)

The user will add new flags in future, so my trigger need to take care of future changes (with unknown column name yet) as well. I think of using a cursor to retrieve all the column name as follow:-

CREATE TRIGGER TALLYMAN.VC
AFTER UPDATE ON TALLYMAN.VC_BULKUPDATES
REFERENCING OLD AS O NEW AS N
OLD_TABLE AS OTAB NEW_TABLE AS NTAB
FOR EACH ROW
BEGIN ATOMIC
DECLARE v_Query VARCHAR(3000);
DECLARE v_columnName, v_ChangeColumn VARCHAR(100);
DECLARE v_NewFlag, v_OldFlag VARCHAR(200);
DECLARE V_CNT INTEGER default 0;
FOR c_Col AS
SELECT NAME from SYSIBM.SYSCOLUMNS
WHERE TBNAME='VC_BULKUPDATES'
AND UPPER(NAME) <> 'ACCOUNTID'
DO
SET v_ColumnName = c_Col.NAME;

SET v_NewFlag = ? ** need to assign New value of FLAG1, FLAG2 ..
SET v_OldFlag = ? ** need to assign Old value of FLAG1, FLAG2 ..

IF (v_NewFlag = 'Y' AND v_OldFlag <> 'Y') THEN
CALL ...
...
ELSE
...
...
END IF;

END FOR;
END

And i'm successfully compile the trigger but is the most challenging time for me to implement the logic.

I'm facing problem to assign the value to v_NewFlag and v_OldFlag for each of the column. Any idea how can i assign it? Or maybe my program flow are not correct?

[Knut Stolze]

Post by c***@talgentra.com
Hi Knut Stolze/tonkuma,
What I'm trying to achieve here is to build a generic trigger that can be
plugged in to A table with any column name. The table is used to stored
all kind of flags with ACCOUNTID as Primary Key. The requirement here is
if Any flag in this table is updated with 'Y', then it will perform
neccessary action.
The table structure will most likely look like this:-
ACCOUNTID DECIMAL(10,0),
FLAG1 VARCHAR(1),
FLAG2 VARCHAR(1),
FLAG3 VARCHAR(1),
FLAG4 VARCHAR(1)
The user will add new flags in future, so my trigger need to take care of
future changes (with unknown column name yet) as well. I think of using a
cursor to retrieve all the column name as follow:-
CREATE TRIGGER TALLYMAN.VC
AFTER UPDATE ON TALLYMAN.VC_BULKUPDATES
REFERENCING OLD AS O NEW AS N
OLD_TABLE AS OTAB NEW_TABLE AS NTAB
FOR EACH ROW
BEGIN ATOMIC
DECLARE v_Query VARCHAR(3000);
DECLARE v_columnName, v_ChangeColumn VARCHAR(100);
DECLARE v_NewFlag, v_OldFlag VARCHAR(200);
DECLARE V_CNT INTEGER default 0;
FOR c_Col AS
SELECT NAME from SYSIBM.SYSCOLUMNS
WHERE TBNAME='VC_BULKUPDATES'
AND UPPER(NAME) <> 'ACCOUNTID'
DO
SET v_ColumnName = c_Col.NAME;
SET v_NewFlag = ? ** need to assign New value of FLAG1, FLAG2 ..
SET v_OldFlag = ? ** need to assign Old value of FLAG1, FLAG2 ..
IF (v_NewFlag = 'Y' AND v_OldFlag <> 'Y') THEN
CALL ...
...
ELSE
...
...
END IF;
END FOR;
END
And i'm successfully compile the trigger but is the most challenging time
for me to implement the logic.
I'm facing problem to assign the value to v_NewFlag and v_OldFlag for each
of the column. Any idea how can i assign it? Or maybe my program flow are
not correct?

I don't see any way to do what you have in mind short of recreating the
trigger. DB2 cannot know what the declared data type of a column to be
added in the future will be. Therefore, it cannot deal with such
non-existing columns when the trigger is created initially.