Trigger not working - Queue problem

[jodo]

I have a trigger below. It fires when an event occurs and an entry is made into the eventlog table. However, it seems that when the event happens in the same second, say a breaker TRIPS CLOSES and TRIPS all in the same second, only the first TRIP causes the trigger to fire. Looks like the trigger is busy firing on the first TRIP and the other CLOSE TRIP event won't fire. Shouldn't trigger events be queued some how?


DECLARE
-- CREATE VARIABLES FOR THIS TRIGGER
v_dtm VARCHAR2(255);
v_pt_name VARCHAR2(255);
v_short_name VARCHAR2(255);
v_v_short_name VARCHAR2(255);
v_id VARCHAR2(255);
v_v_id VARCHAR2(255);
v_sub VARCHAR2(255);
v_breaker VARCHAR2(255);
v_breaker_trim VARCHAR2(255);
v_gens_breaker VARCHAR2(255);
v_text VARCHAR2(255);
v_tag VARCHAR2(255);
v_v_tag VARCHAR2(255);
v_flag NUMBER;
CURSOR cur IS SELECT id, tag, short_name FROM lakdba.breaker_tags;
CURSOR cur2 IS SELECT breaker FROM lakdba.gens_not_acc;
--**************************************************************************
------------------------------------------------------------------------------
-----Determines Trip/Close--------------------------------
FUNCTION TRIPCLOSE (v_text VARCHAR2) RETURN VARCHAR2 IS
RETURN_VALUE VARCHAR2(3);
BEGIN
IF SUBSTR(trim(v_text), 1)='TRIP' THEN
RETURN_VALUE:='T';
ELSIF SUBSTR(trim(v_text), 1)='CLOSE' THEN
RETURN_VALUE:='C';
ELSIF SUBSTR(trim(v_text), 1)='CLOSE TRIP' THEN
RETURN_VALUE:='CT';
ELSIF SUBSTR(trim(v_text), 1)='TRIP CLOSE' THEN
RETURN_VALUE:='TC';
ELSIF SUBSTR(trim(v_text), 1)='TRIP CLOSE TRIP' THEN
RETURN_VALUE:='TCT';
ELSIF SUBSTR(trim(v_text), 1)='CLOSE TRIP CLOSE' THEN
RETURN_VALUE:='CTC';
ELSE
RETURN_VALUE:=NULL;
END IF;

RETURN(RETURN_VALUE);
END TRIPCLOSE;
---------------------------------------------------------------------------
BEGIN
v_flag:=0;
v_pt_name:=:new.pointname;
v_text:=SUBSTRnew.alarmtext,103);

--/*
--GETS THE POINTS WE DO WANT BY COMPARING ALL OF THE BREAKER OPERATION
--POINTNAMES TO ACCEPTABLE VALUES ENTERED INTO THE lakdba.breaker_tags TABLE.
OPEN cur; LOOP FETCH cur INTO v_id, v_tag, v_short_name;
EXIT WHEN NOT cur%FOUND;
IF INSTR(v_pt_name, v_id) > 0 THEN
v_text:=TRIPCLOSE(v_text);
IF v_text IS NOT NULL THEN
v_flag:=1;
v_v_tag:=v_tag;
v_v_id:=v_id;
v_v_short_name:=v_short_name;
END IF;
END IF;
END LOOP; CLOSE cur;
--*/
--FILTERS OUT THE POINTS WE DO WANT BY COMPARING ALL OF THE BREAKER OPERATION
--POINTNAMES TO UNACCEPTABLE VALUES ENTERED INTO THE lakdba.gens_not_acc TABLE.
--THESE ARE GENERATOR BREAKERS
OPEN cur2; LOOP FETCH cur2 INTO v_gens_breaker;
EXIT WHEN NOT cur2%FOUND;
IF INSTR(v_pt_name, v_gens_breaker) > 0 THEN
v_flag:=0;
END IF;
END LOOP; CLOSE cur2;

IF v_flag = 1 THEN

--/*-----Determines Sub--------------------------
IF v_v_tag='BREED' THEN
v_sub:='BRU';
ELSIF v_v_tag= 'RECL' THEN
v_sub:='RECBB';
ELSIF v_v_tag='CONT' THEN
v_sub:='CPU';
ELSIF v_v_tag='BUTTER' THEN
v_sub:='BKU';
ELSE
FOR i IN 1..LENGTH(v_pt_name) LOOP
IF SUBSTR(v_pt_name,i) < 'A' AND v_sub IS NULL THEN
v_sub:=SUBSTR(v_pt_name,1,i-1);
END IF;
END LOOP;
END IF;
--*/-------------------------------------------------

--/*-----Determines Breaker--------------------------
IF v_v_tag='RECL' THEN
--v_breaker:='REC' || Substrnew.pointname,1,INSTRnew.pointname,' ',1 ,1));
v_breaker:=v_v_short_name;
ELSIF v_v_tag='BREED' THEN
v_breaker:=v_v_short_name;
ELSIF v_v_tag='CONT' THEN
v_breaker:=v_v_short_name;
ELSIF v_v_tag='BUTTER' THEN
v_breaker:=v_v_short_name;
ELSIF v_v_tag='C-344' THEN
v_breaker:=v_v_short_name;
ELSIF v_v_tag='C-354' THEN
v_breaker:=v_v_short_name;
ELSE
v_breaker:=Substrnew.pointname,1,INSTRnew.pointname,' ',1 ,1));
END IF;
--*/--------------------------------------------------

--v_sub:='J';
--v_breaker:='J364';
--v_text:='T';
v_dtm:=to_char((LAKDBA.pk_lk_util.fn_utc2locnew.utctime)), 'yyyy/mm/dd HH24:MI:SS');
-- v_dtm:=to_charnew.utctime, 'yyyy/mm/dd HH24:MI:SS');

-------LIVE TABLE ON GISDB1----------
INSERT INTO BREAKERDBA.BREAKER@ISR2GISDB1(subs, breaker, tripclose, breakerdate, tagpos)
VALUES(trim(v_sub), trim(v_breaker), trim(v_text), v_dtm, 2);

--INSERT INTO LAKDBA.BREAKER(subs, breaker, tripclose, breakerdate)

------TEST TABLE ON GISDB1-----------
INSERT INTO BREAKERDBA.BREAKER_TEST@ISR2GISDB1(subs, breaker, tripclose, breakerdate, tagpos)
VALUES(trim(v_sub), trim(v_breaker), trim(v_text), v_dtm, 2);

------TEST TABLE ON ISR -------------
INSERT INTO LAKDBA.BREAKER(subs, breaker, tripclose, breakerdate, tagpos)
VALUES(trim(v_sub), trim(v_breaker), trim(v_text), v_dtm, 2);


END IF;
EXCEPTION
WHEN OTHERS THEN
return;
END;


jodom
LUCK - WHERE PREPARATION MEETS OPPORTUNITY!!

 

[Thargy]

jodo,

I tend to agree, I believe trigger events should queue up nicely, but I can't find any documentation to substantiate that.

However, can you clarify something for me please. You say that it fires and an entry is made in to the event log table. Is the trigger fired after an insert into the event log table, for each row, or is it something else that fires it?

From my days in the industry, I recall that SCADA systems were used to control switchgear, and they used dedicated hardware to give milli or microsecond resolution event logging. I presume that your table is being populated by some such system. Is this correct?

T

Grinding away at things Oracular

 

[jodo]

It fires when an entry is made into the eventlog table. The eventlog receives its entries from many different events in the field. However, my trigger only captures breaker operations. The trigger is fired after an INSERT into the eventlog table for each row. Yes, this is a SCADA system.

I just can't figure out why it misses entries that occur on the same second.

Thanks for your help.

jodom
LUCK - WHERE PREPARATION MEETS OPPORTUNITY!!

 

[Thargy]

jodo,

can you please post create table statements for the event log, the full trigger statement, and also some sample data which demonstrates the problem.

I am stumped at present, but suggest that you try the following:-

Make an event log, and put 3 dummy breaker entries into it with differing seconds, and verify that the trigger fires 3 times. Then clear the table, do the same thing again, but put two entries in the same second. Can you finagle your SCADA system to generate the dud data for you?

What I'm driving at is systematically isolating the time, number and type of entries, to see which of these (either singly or in combination) is causing the problem.

Regards

T

Grinding away at things Oracular

 

[Thargy]

jodo,

I've been thinking (hence the long delay and the smell of burning) and I wonder if the problem really is due to the "same second" fooling Oracle into only firing the trigger once.

My reasoning is that if Oracle is correctly inserting three separate records into the event log (even though they occurred within the same second) then Oracle must "know" about the three separate logs, otherwise how did it correctly differentiate between them, when it inserted? If it knew about the three separate logs, then it knew about three separate events, and knew to fire the trigger three times.

Can you see a hole in that logic? It's late, and I can't, but please check my thinking. You may be barking up the wrong tree.

Regards

T

Grinding away at things Oracular

 

[jodo]

EVENTLOG TABLE CREATE STATEMENT

------- Generated by SYS.DBMS_METADATA on 3/25/2008 at 10:00:45 AM -------

CREATE TABLE "LAKEMS"."EVENTLOG"
( "UTCTIME" DATE,
"POINTNAME" VARCHAR2(25 CHAR),
"POINTTYPE" NUMBER(*,0),
"POINTNUMBER" NUMBER(*,0),
"STATIONNAME" VARCHAR2(30 CHAR),
"PRIORITYDESCRIPTION" VARCHAR2(16 CHAR),
"PRIORITY" NUMBER(*,0),
"ALARMCLASS" NUMBER(*,0),
"EVENTCODE" NUMBER(*,0),
"ALARMTEXT" VARCHAR2(255 CHAR),
"POINTVALUE" FLOAT(126),
"STATENAME" VARCHAR2(16 CHAR),
"TLQ" NUMBER(*,0),
"AORMASKGRP1" VARCHAR2(8 CHAR),
"AORMASKGRP2" VARCHAR2(8 CHAR),
"ALARMFLAG" NUMBER(*,0),
"EVENTFLAG" NUMBER(*,0),
"MOD_DATE" DATE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "USERS"
MONITORING

/

------------------------------------------------------

FULL TRIGGER STATEMENT

------- Generated by SYS.DBMS_METADATA on 3/25/2008 at 9:59:33 AM -------

CREATE OR REPLACE TRIGGER "LAKDBA"."TR_BREAKER_OPS_AUTO_INSERT"

AFTER
INSERT
ON LAKEMS.EVENTLOG
REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
DECLARE
-- CREATE VARIABLES FOR THIS TRIGGER
v_dtm VARCHAR2(255);
v_pt_name VARCHAR2(255);
v_short_name VARCHAR2(255);
v_v_short_name VARCHAR2(255);
v_id VARCHAR2(255);
v_v_id VARCHAR2(255);
v_sub VARCHAR2(255);
v_breaker VARCHAR2(255);
v_breaker_trim VARCHAR2(255);
v_gens_breaker VARCHAR2(255);
v_text VARCHAR2(255);
v_tag VARCHAR2(255);
v_v_tag VARCHAR2(255);
v_flag NUMBER;
CURSOR cur IS SELECT id, tag, short_name FROM lakdba.breaker_tags;
CURSOR cur2 IS SELECT breaker FROM lakdba.gens_not_acc;
--**************************************************************************
------------------------------------------------------------------------------
-----Determines Trip/Close--------------------------------
FUNCTION TRIPCLOSE (v_text VARCHAR2) RETURN VARCHAR2 IS
RETURN_VALUE VARCHAR2(3);
BEGIN
IF SUBSTR(trim(v_text), 1)='TRIP' THEN
RETURN_VALUE:='T';
ELSIF SUBSTR(trim(v_text), 1)='CLOSE' THEN
RETURN_VALUE:='C';
ELSIF SUBSTR(trim(v_text), 1)='CLOSE TRIP' THEN
RETURN_VALUE:='CT';
ELSIF SUBSTR(trim(v_text), 1)='TRIP CLOSE' THEN
RETURN_VALUE:='TC';
ELSIF SUBSTR(trim(v_text), 1)='TRIP CLOSE TRIP' THEN
RETURN_VALUE:='TCT';
ELSIF SUBSTR(trim(v_text), 1)='CLOSE TRIP CLOSE' THEN
RETURN_VALUE:='CTC';
ELSE
RETURN_VALUE:=NULL;
END IF;

RETURN(RETURN_VALUE);
END TRIPCLOSE;
---------------------------------------------------------------------------
BEGIN
v_flag:=0;
v_pt_name:=:new.pointname;
v_text:=SUBSTRnew.alarmtext,103);

--/*
--GETS THE POINTS WE DO WANT BY COMPARING ALL OF THE BREAKER OPERATION
--POINTNAMES TO ACCEPTABLE VALUES ENTERED INTO THE lakdba.breaker_tags TABLE.
OPEN cur; LOOP FETCH cur INTO v_id, v_tag, v_short_name;
EXIT WHEN NOT cur%FOUND;
IF INSTR(v_pt_name, v_id) > 0 THEN
v_text:=TRIPCLOSE(v_text);
IF v_text IS NOT NULL THEN

v_flag:=1;
v_v_tag:=v_tag;
v_v_id:=v_id;
v_v_short_name:=v_short_name;
END IF;
END IF;
END LOOP; CLOSE cur;
--*/
--FILTERS OUT THE POINTS WE DO WANT BY COMPARING ALL OF THE BREAKER OPERATION
--POINTNAMES TO UNACCEPTABLE VALUES ENTERED INTO THE lakdba.gens_not_acc TABLE.
--THESE ARE GENERATOR BREAKERS
OPEN cur2; LOOP FETCH cur2 INTO v_gens_breaker;
EXIT WHEN NOT cur2%FOUND;
IF INSTR(v_pt_name, v_gens_breaker) > 0 THEN
v_flag:=0;
END IF;
END LOOP; CLOSE cur2;

IF v_flag = 1 THEN

--/*-----Determines Sub--------------------------
IF v_v_tag='BREED' THEN
v_sub:='BRU';
ELSIF v_v_tag= 'RECL' THEN
v_sub:='RECBB';
ELSIF v_v_tag='CONT' THEN
v_sub:='CPU';
ELSIF v_v_tag='BUTTER' THEN
v_sub:='BKU';
ELSE
FOR i IN 1..LENGTH(v_pt_name) LOOP
IF SUBSTR(v_pt_name,i) < 'A' AND v_sub IS NULL THEN
v_sub:=SUBSTR(v_pt_name,1,i-1);
END IF;
END LOOP;
END IF;
--*/-------------------------------------------------

--/*-----Determines Breaker--------------------------
IF v_v_tag='RECL' THEN
--v_breaker:='REC' || Substrnew.pointname,1,INSTRnew.pointname,' ',1 ,1));
v_breaker:=v_v_short_name;
ELSIF v_v_tag='BREED' THEN
v_breaker:=v_v_short_name;
ELSIF v_v_tag='CONT' THEN
v_breaker:=v_v_short_name;
ELSIF v_v_tag='BUTTER' THEN
v_breaker:=v_v_short_name;
ELSIF v_v_tag='C-344' THEN
v_breaker:=v_v_short_name;
ELSIF v_v_tag='C-354' THEN
v_breaker:=v_v_short_name;
ELSE
v_breaker:=Substrnew.pointname,1,INSTRnew.pointname,' ',1 ,1));
END IF;
--*/--------------------------------------------------

--v_sub:='J';
--v_breaker:='J364';
--v_text:='T';
v_dtm:=to_char((LAKDBA.pk_lk_util.fn_utc2locnew.utctime)), 'yyyy/mm/dd HH24:MI:SS');
-- v_dtm:=to_charnew.utctime, 'yyyy/mm/dd HH24:MI:SS');

-------LIVE TABLE ON GISDB1----------
INSERT INTO BREAKERDBA.BREAKER@ISR2GISDB1(subs, breaker, tripclose,
breakerdate, tagpos)
VALUES(trim(v_sub), trim(v_breaker), trim(v_text), v_dtm, 2);

--INSERT INTO LAKDBA.BREAKER(subs, breaker, tripclose, breakerdate)

------TEST TABLE ON GISDB1-----------
INSERT INTO BREAKERDBA.BREAKER_TEST@ISR2GISDB1(subs, breaker, tripclose, breakerdate, tagpos)
VALUES(trim(v_sub), trim(v_breaker), trim(v_text), v_dtm, 2);

------TEST TABLE ON ISR -------------
INSERT INTO LAKDBA.BREAKER(subs, breaker, tripclose, breakerdate, tagpos)
VALUES(trim(v_sub), trim(v_breaker), trim(v_text), v_dtm, 2);


END IF;
EXCEPTION
WHEN OTHERS THEN
return;
END;

--------------------------------------------------------------------------------------

ALTER TRIGGER "LAKDBA"."TR_BREAKER_OPS_AUTO_INSERT" ENABLE

/
------------------------------------------------------

SAMPLE DATA

Q Q314 T 2008/03/16 19:46:40
W W374 T 2008/03/20 06:35:22
W W374 C 2008/03/20 06:35:22
W W374 T 2008/03/20 06:36:40
W W374 C 2008/03/20 06:36:40
W W374 T 2008/03/20 06:36:40
W W374 C 2008/03/20 06:36:48
V V374 TC 2008/03/20 08:08:41
Y Y364 TCT 2008/03/22 15:11:48
Y Y374 TC 2008/03/22 15:11:48

For example, the operation that occurred on 2008/03/20 06:36:40 for breaker W374 that T (tripped), then C (closed), then T (tripped) wasn't originally fully picked up by the trigger, only the initial T. However, I do have test tables that you mentioned in your previous message that I have set up and when I truncated the table, then re-inserted data into the dummy eventlog table, the trigger fired. It's like hit or miss. It's driving me crazy.

I've looked historically at my test table and I have received 'TCT' and 'CTC' and 'TC' and 'CT' combinations that have worked via the trigger before.

Thanks for your help.



jodom
LUCK - WHERE PREPARATION MEETS OPPORTUNITY!!

 

[Thargy]

jodom,

an initial perusal of your code reveals no obvious 'smoking gun', although, for some other time, I believe the trigger would benefit from some simplification.

Back to the problem at hand, i.e. the trigger failing to fire. You say

I've looked historically at my test table and I have received 'TCT' and 'CTC' and 'TC' and 'CT' combinations that have worked via the trigger before.

Does this historical list contain one or more instances where the trigger has fired for a CTC combination where the time stamp was the same? If it does, then you have conclusive proof that the trigger is not faulty, but the system design is (because it is acting in a non-deterministic way).

At that point, I would look around for other triggers on either the same or an assoicated table. Since their firing order is not known or knowable, they are a classic way of cocking-up, and elegantly provide chaotic behaviour.

And the answer is?

T

Grinding away at things Oracular