Hello Experts

I have to create a trigger for Keep Track of Inserts, Delete and Update of a table by recoding that change in another table

Here is the scenario

Refer attachments for Table Structure

CASE 1
I have 2 tables DTV_CHANNELS_TEST and DTV_CONTROL_TEST

In DTV_CHANNELS_TEST when a user insert a record where ACTIVE_STATUS = 1
I need to do the following action to DTV_CONTROL_TEST table using a trigger

1. Count all the records where ACTIVE_STATUS =1 and insert to NO_OF_CHANNELS column
2. Insert CURRENT TIMESTAMP to LAST_UPDATE_DATE column
3. Insert CURRENT USER to USER_ENTERED column
4. Insert CURRENT TIMESTAMP to DATE_ENTERED column

This trigger should fire only if user insert a record with ACTIVE_STATUS = 1

CASE 2

When user update the records

If user change the ACTIVE_STATUS
Then
NO_OF_CHANNELS should increment or decrement [When user change ACTIVE_STATUS = 1 or 0] + Insert CURRENT TIMESTAMP to LAST_UPDATE_DATE column

If user change column except ACTIVE_STATUS
Then
Insert CURRENT TIMESTAMP to LAST_UPDATE_DATE column only

SO i was stuck with the insert trigger...

Plz note that DTV_CONTROL_TEST table maintain 1 column and it will update every time triggers fire

Appreciate ur help

Have you tried creating any triggers for the above scenarios?

If so, please post what you have tried. Makes it easier for the "Experts" to build on ;-)


---

CREATE OR REPLACE TRIGGER "DB2ADMIN"."TRIG_DTV_CONTROL_INSERT"
AFTER INSERT
ON "DB2ADMIN"."DTV_CHANNELS_TEST"
REFERENCING
NEW AS "NEWROW"
FOR EACH ROW
INSERT INTO DTV_CONTROL_TEST
(NO_OF_CHANNELS,LAST_UPDATE_DATE,USER_ENTERED,DATE _ENTERED)
VALUES ((SELECT COUNT(*) FROM DTV_CHANNELS_TEST),CURRENT TIMESTAMP,CURRENT USER,CURRENT TIMESTAMP)
WHERE NEWROW.ACTIVE_STATUS = 1;

I have tried this way for the where statements in inserts

This one created in the DB but when i try to insert
----
com.ibm.db2.jcc.am.SqlException: An error occurred in a triggered SQL statement in trigger "DB2ADMIN.TRIG_DTV_CONTROL". Information returned for the error includes SQLCODE "-407", SQLSTATE "23502" and message tokens "TBSPACEID=2, TABLEID=272, COLNO=0".. SQLCODE=-723, SQLSTATE=09000, DRIVER=4.8.86
Error saving data
----
CREATE TRIGGER "DB2ADMIN"."TRIG_DTV_CONTROL_INSERT"
AFTER INSERT
ON "DB2ADMIN"."DTV_CHANNELS_TEST"
REFERENCING
NEW AS "NEWROW"
FOR EACH ROW
INSERT INTO DTV_CONTROL_TEST (NO_OF_CHANNELS,LAST_UPDATE_DATE,USER_ENTERED,DATE _ENTERED)
SELECT (
SELECT COUNT(*)
FROM DTV_CHANNELS_TEST ),CURRENT TIMESTAMP,CURRENT USER,CURRENT TIMESTAMP
FROM SYSIBM.SYSDUMMY1
WHERE EXISTS (
SELECT *
FROM DTV_CHANNELS_TEST
WHERE ACTIVE_STATUS = 1);

both of them didn't work..

Use UPDATE statement instead of INSERT statement in the trigger.

CREATE OR REPLACE TRIGGER "DB2ADMIN"."TRIG_DTV_CONTROL_INSERT"
AFTER INSERT
ON "DB2ADMIN"."DTV_CHANNELS_TEST"
REFERENCING
NEW AS "NEWROW"
FOR EACH ROW
UPDATE DTV_CONTROL_TEST
SET NO_OF_CHANNELS = (SELECT COUNT(*) FROM DTV_CHANNELS_TEST WHERE ACTIVE_STATUS = 1),LAST_UPDATE_DATE = CURRENT TIMESTAMP , USER_ENTERED = CURRENT USER , DATE_ENTERED = CURRENT TIMESTAMP
WHERE (SELECT ACTIVE_STATUS FROM DTV_CHANNELS WHERE ACTIVE_STATUS = 1) = 1;

ERROR
com.ibm.db2.jcc.am.SqlException: An error occurred in a triggered SQL statement in trigger "DB2ADMIN.TRIG_DTV_CONTROL_INSERT". Information returned for the error includes SQLCODE "-811", SQLSTATE "21000" and message tokens "".. SQLCODE=-723, SQLSTATE=09000, DRIVER=4.8.86
Error saving data

You get SQLCODE 407 because you have defined CTRL_ID as NOT NULL but not inserting anything into that field. Correct that one.

a) One option - Instead of doing a count(*), why not add 1 to the NO_OF_CHANNELS when the active_status is 1 in the insert or update and subtract 1 when the active_status is 0.

b) You must have a delete trigger also if you have to keep the count of active_status current.

c) CREATE TRIGGER ...
AFTER IJNSERT ..
WHEN (NEWROW.ACTIVE_STATUS=1) THEN
<your triggered action>

This will avoid using the WHERE clause in the query.

d) What is the purpose of the CONTROL table ? If it is to know the current status, then you have to use UPDATE. If it is for History then you can use INSERT.

Hope this makes sense

Your SQLCODE 811 is

SQL0811N The result of a scalar fullselect, SELECT INTO statement, or
VALUES INTO statement is more than one row.

Who's creating the table definitions and who creating these trigger specficiations ?


--

SQLCODE "-811" must be issued from this subselect.
(SELECT ACTIVE_STATUS FROM DTV_CHANNELS WHERE ACTIVE_STATUS = 1)

See sathyaram_s' post, especially...

CREATE OR REPLACE TRIGGER "DB2ADMIN"."TRIG_DTV_CONTROL_INSERT"
AFTER INSERT
ON "DB2ADMIN"."DTV_CHANNELS_TEST"
REFERENCING
NEW AS "NEWROW"
FOR EACH ROW
WHEN (NEWROW.ACTIVE_STATUS=1) THEN
INSERT INTO DTV_CONTROL_TEST (NO_OF_CHANNELS,LAST_UPDATE_DATE,USER_ENTERED,DATE _ENTERED)
VALUES ((SELECT COUNT(*) FROM DTV_CHANNELS_TEST WHERE ACTIVE_STATUS = 1),CURRENT TIMESTAMP,CURRENT USER,CURRENT TIMESTAMP)
;

ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "THEN INSERT" was found following "ROW.ACTIVE_STATUS=1)". Expected tokens may include: "<insert>". LINE NUMBER=1. SQLSTATE=42601

CREATE TRIGGER - IBM DB2 9.7 for Linux, UNIX, and Windows

Have you seen this page?

And you don't seem to have paid attention to SQLCODE -407
I'm afraid, you have to go back to the design/specification and understand what and why it has been done that way. There appear to be gaps, IMHO.

See the syntax of triggered-action and Example 2: in CREATE TRIGGER statement
CREATE TRIGGER - IBM DB2 9.7 for Linux, UNIX, and Windows

Note: "BEGIN ATOMIC" and "END" are not neccesary for a triggered-action with one statement.

Create or replace trigger "db2admin"."trig_dtv_control_insert"
after insert
on "db2admin"."dtv_channels_test"
referencing
new as "newrow"
for each row
when (newrow.active_status=1) then
begin atomic
insert into dtv_control_test (no_of_channels,last_update_date,user_entered,date _entered)
values ((select count(*) from dtv_channels_test where active_status = 1),current timestamp,current user,current timestamp);
end
;

create trigger "db2admin"."trig_dtv_control_insert"
after insert of channel_id, channel_logo, default_url, active_status, channel_type on "db2admin"."dtv_channels_test"
referencing new as "newrow"
for each row
when (newrow.active_status=1)
begin atomic
insert into dtv_control_test (no_of_channels,last_update_date,user_entered,date _entered)
values ((select count(*) from dtv_channels_test where active_status = 1),current timestamp,current user,current timestamp);
end

Im getting errors for above to methods

DB2 Database Error: ERROR [428HV] [IBM][DB2/NT] SQL0969N There is no message text corresponding to SQL error "-20521" in the message file on this workstation. The error was returned from module "SQLNP02C" with original tokens "_entered 6". LINE NUMBER=1. SQLSTATE=428HV


DB2 Database Error: ERROR [428HV] [IBM][DB2/NT] SQL0969N There is no message text corresponding to SQL error "-20521" in the message file on this workstation. The error was returned from module "SQLNP02C" with original tokens "_entered 6". LINE NUMBER=7. SQLSTATE=428HV

is it possible that you have a space in
,date _entered)