Home » SQL & PL/SQL » SQL & PL/SQL » problem with trigger
problem with trigger [message #340846] |
Thu, 14 August 2008 10:15  |
s_ali_hassan
Messages: 18 Registered: August 2008
|
Junior Member |
|
|
Hi,
I am having a problem with the following trigger:
CREATE Trigger test2
BEFORE INSERT OR UPDATE on TableA
FOR EACH ROW
DECLARE
numb number(2);
asteamid varchar2(40);
asteamname varchar2(40);
agtid varchar2(40);
agtname varchar2(40);
tid varchar2(40);
tname varchar2(40);
BEGIN
agtid := :new.creatorid;
agtname := :new.creatorname;
numb := 0;
If :new.agentid is null or :new.agentname is null then
:new.comments := :new.teamid;
:new.outcome := :new.teamname;
asteamid := :new.teamid;
Select COUNT(*) INTO numb FROM TableB where teamid=asteamid and teamleader='1';
:new.outcome := numb;
If numb = 0 then
SELECT ag.teamid, t.teamname INTO tid, tname FROM TableB ag, TableC t
WHERE ag.netgainid= :new.updatorid AND ag.teamid=t.netgainid;
:new.agentid := agtid;
:new.agentname := agtname;
:new.teamid := tid;
:new.teamname := tname;
END If;
END If;
END test2;
The problem i am having is that the trigger is assigning the incorrect value to 'numb'.
If the 'select count(*)' query brings back a value of more than 0 (i.e 1, 2, 3 etc), the trigger always evaluates numb to be 0.
However, if i remove the 'IF numb = 0' block, then the correct assignment is made to numb.
I am looking at the reults on the front end application.
I have tried to work out why this is happening but have failed to do so.
Has anyone got any thoughts on why this is happening or how to get around it?
Thanks
(i will check all your responses tomorrow, so if you have any questions i will not be able to answer promptly)
|
|
|
|
Re: problem with trigger [message #340946 is a reply to message #340849] |
Fri, 15 August 2008 02:43   |
s_ali_hassan
Messages: 18 Registered: August 2008
|
Junior Member |
|
|
I have a front end application in which some of the insert values appear.
I have assigned 'numb' value to ':new.outcome'. This field appears in the front end so i can see what numb is assigned as.
I have been trying to find out why i am having the problem, but it is very puzzling as to why numb evaluates to 0 with the IF block in place, but without it, numb's value evaluates correctly.
|
|
|
|
Re: problem with trigger [message #340956 is a reply to message #340948] |
Fri, 15 August 2008 03:19   |
s_ali_hassan
Messages: 18 Registered: August 2008
|
Junior Member |
|
|
I am not sure i undertand you, but i will try and explain it again (this time with a bit more background).
Initially i had one trigger, but then spllit it into 2 so that i could debug.
What i initially had:
CREATE Trigger activities
BEFORE INSERT OR UPDATE on TableA
FOR EACH ROW
DECLARE
numb number(2);
asteamid varchar2(40);
asteamname varchar2(40);
agtid varchar2(40);
agtname varchar2(40);
tid varchar2(40);
tname varchar2(40);
BEGIN
If :new.agentid is null or :new.agentname is null then
asteamid := :new.teamid;
asteamname := :new.teamname;
:new.comments := :new.teamid;
Select COUNT(*) INTO numb FROM TableB where teamid=asteamid and teamleader='1';
:new.outcome := numb;
if numb >= 1 then
SELECT agentname, netgainid INTO agtname, agtid
FROM TableB WHERE teamid=asteamid and teamleader='1' and rownum<=1;
:new.agentid := agtid;
:new.agentname := agtname;
END If;
If numb = 0 then
agtid := :new.creatorid;
agtname := :new.creatorname;
SELECT ag.teamid, t.teamname INTO tid, tname FROM TableB ag, TableC t
WHERE ag.netgainid=agtid AND ag.teamid=t.netgainid;
:new.teamid := tid;
:new.teamname := tname;
:new.agentid := agtid;
:new.agentname := agtname;
END If;
END If;
END activities;
So on the initial insert the code always evaluates numb to be 0, i.e no teamleaders. However, i KNOW that for the team selected, there are teamleaders and therefore numb should not evaluate to 0. This is demonstrated when i ommit both the IF blocks (for purposes of debugging) and then notice that numb is not set to 0 (but some other value which corresponds to the number of teamleaders) for the same inserted values. I see this on the front end application.
When the record is UPDATED, the code works perfectly, as in the correct assignment is made to numb and therefore it goes into the correct IF block and the expected behaviour is experienced.
The ':new.outcome := numb;' is there purely for debugging purposes.
I hope i have made myself clear.
[Updated on: Fri, 15 August 2008 06:54] by Moderator Report message to a moderator
|
|
|
|
Re: problem with trigger [message #340970 is a reply to message #340964] |
Fri, 15 August 2008 06:12   |
s_ali_hassan
Messages: 18 Registered: August 2008
|
Junior Member |
|
|
It is the same trigger, but just with logic on what to do if numb >= 1.
I have searched all the triggers on the database, and there are none that affect TableA.
It is strange, in that if i ommit the second IF block (when numb = 0) then the trigger works fine (but it obviously does nothing when numb really does = 0).
I tried to split the triggers as mentioned earlier (one for each part of the if block) but this did not work. Also, i looked into if i could fire one first then the other Following it, but i cannot do this as i am using Oracle 10g, and as far as i am aware the FOLLOWS statement can only be used in 11g.
Anyone know what else i can try?
[Updated on: Fri, 15 August 2008 06:16] Report message to a moderator
|
|
|
Re: problem with trigger [message #340971 is a reply to message #340956] |
Fri, 15 August 2008 06:13   |
JRowbottom
Messages: 5933 Registered: June 2006 Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
I think we've got to the point here where we need substantially more from you. The problem as you describe it, given the available information makes no sense.
Can you provie us with:
1) CREATE TABLE statements for TableA and TableB
2) Some Insert statements to populate TableB
That way, we can try and reproduce your problem, and your chances of a solution go up significantly.
|
|
|
Re: problem with trigger [message #340976 is a reply to message #340971] |
Fri, 15 August 2008 06:52   |
s_ali_hassan
Messages: 18 Registered: August 2008
|
Junior Member |
|
|
Basic layout of the tables. The tables actually have more fields, but they are not relevant and nothing happens to them.
TableA:
Uniqueid(primary Key)
Teamid(foreign key to TableC.uniqueid)
Teamname
Creatorname
Creatorid (foreign key to TableB.uniqueid)
Agentname
Agentid(foreign key to TableB.uniqueid)
Comments
TableB:
Uniqueid(lPrimary key)
Teamid (foreign key to TableC.uniqueid)
Teamleader
TableC:
Uniqueid(primary key)
Teamname
Not sure these work, as I have not tried them (not sure if the foreign key stuff works as I have never done before):
CREATE TABLE TableA (Uniqueid VarChar2 PRIMARYKEY,
Teamid VarChar2 REFERENCES TableC(uniqueid),
Teamname VarChar2,
Creatorname VarChar2,
Creatorid VarChar2
REFERENCES TableB(uniqueid),
Agentname VarChar2,
Agentid VarChar2 REFERENCES TableB(uniqueid),
Comments VarChar2);
CREATE TABLE TableB (Uniqueid VarChar2 PRIMARY KEY,
Teamid VarChar2 REFERENCES TableC(uniqueid),
Teamleader NUM);
CREATE TABLE TableC (Uniqueid VarChar2 PRIMARYKEY,
Teamname VarChar2)
ALTER TABLE TableA ADD CONSTRAINT TableAREFTableC
FOREIGN KEY (Teamid) REFERENCES TableC(Uniqueid)
INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE TableA ADD CONSTRAINT TableAREFTableB
FOREIGN KEY (Agentid) REFERENCES TableB(Uniqueid)
INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE TableA ADD CONSTRAINT TableAREFTableB
FOREIGN KEY (Creatorid) REFERENCES TableB(Uniqueid)
INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE TableB ADD CONSTRAINT TableAREFTableC
FOREIGN KEY (Teamid) REFERENCES TableC(Uniqueid)
INITIALLY DEFERRED DEFERRABLE;
(dont know what the initially deferred means)
Please note that on TableA, Creatorid and Agentid can be the same value or different.
What I am doing is trying to populate TableA. Sample INSERT statements (not sure if its what your looking for – just let me know and I’ll adjust it):
INSERT INTO TableA VALUES(123456, 1, team1, person1, 1, person2, 2, blabla );
INSERT INTO TableB VALUES(1, 1, 1), (2, 1, 0), (3, 2, 1), (4, 2, 0), (5, 3, 0);
INSERT INTO TableC VALUE(1, team1), (2, team2), (3, team3);
[Updated on: Fri, 15 August 2008 06:53] by Moderator Report message to a moderator
|
|
|
|
Re: problem with trigger [message #340982 is a reply to message #340976] |
Fri, 15 August 2008 07:02   |
s_ali_hassan
Messages: 18 Registered: August 2008
|
Junior Member |
|
|
Tried to edit my post but it didnt work for some strange reason.
Basic layout of the tables. The tables actually have more fields, but they are not relevant and nothing happens to them.
TableA:
Uniqueid(primary Key)
Teamid(foreign key to TableC.uniqueid)
Teamname
Creatorname
Creatorid (foreign key to TableB.uniqueid)
Agentname
Agentid(foreign key to TableB.uniqueid)
Comments
TableB:
Uniqueid(lPrimary key)
Teamid (foreign key to TableC.uniqueid)
Teamleader
TableC:
Uniqueid(primary key)
Teamname
Not sure these work, as I have not tried them (not sure if the foreign key stuff works as I have never done before):
CREATE TABLE TableA (Uniqueid VarChar2 PRIMARYKEY,
Teamid VarChar2 REFERENCES TableC(uniqueid),
Teamname VarChar2,
Creatorname VarChar2,
Creatorid VarChar2
REFERENCES TableB(uniqueid),
Agentname VarChar2,
Agentid VarChar2 REFERENCES TableB(uniqueid),
Comments VarChar2);
CREATE TABLE TableB (Uniqueid VarChar2 PRIMARY KEY,
Teamid VarChar2 REFERENCES TableC(uniqueid),
Teamleader NUM);
CREATE TABLE TableC (Uniqueid VarChar2 PRIMARYKEY,
Teamname VarChar2)
ALTER TABLE TableA ADD CONSTRAINT TableAREFTableC
FOREIGN KEY (Teamid) REFERENCES TableC(Uniqueid)
INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE TableA ADD CONSTRAINT TableAREFTableB
FOREIGN KEY (Agentid) REFERENCES TableB(Uniqueid)
INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE TableA ADD CONSTRAINT TableAREFTableB
FOREIGN KEY (Creatorid) REFERENCES TableB(Uniqueid)
INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE TableB ADD CONSTRAINT TableAREFTableC
FOREIGN KEY (Teamid) REFERENCES TableC(Uniqueid)
INITIALLY DEFERRED DEFERRABLE;
(dont know what the initially deferred means)
Please note that on TableA, Creatorid and Agentid can be the same value or different.
What I am doing is trying to populate TableA. Sample INSERT statements (not sure if its what your looking for – just let me know and I’ll adjust it):
INSERT INTO TableA VALUES(123456, 1, team1, person1, 1, person2, 2, blabla );
INSERT INTO TableB VALUES(1, 1, 1), (2, 1, 0), (3, 2, 1), (4, 2, 0), (5, 3, 0);
INSERT INTO TableC VALUE(1, team1), (2, team2), (3, team3);
Which ones are the invalid statements?
Are they not 80 characters???????
|
|
|
|
Re: problem with trigger [message #341002 is a reply to message #340988] |
Fri, 15 August 2008 08:31   |
s_ali_hassan
Messages: 18 Registered: August 2008
|
Junior Member |
|
|
little busy at the moment.
I will try and amend for oracle (i wasnt aware, i just wnet to the first google website i founf regarding insert statements! - foolish i know)
i will amend in a while
|
|
|
|
|
Re: problem with trigger [message #341021 is a reply to message #340982] |
Fri, 15 August 2008 09:33   |
s_ali_hassan
Messages: 18 Registered: August 2008
|
Junior Member |
|
|
CREATE TABLE TableA (
Uniqueid VARCHAR2(40),
Teamid VARCHAR2(40),
Teamname VARCHAR2(40),
Creatorname VARCHAR2(40),
Creatorid VARCHAR2(40),
Agentname VARCHAR2(40),
Agentid VARCHAR2(40),
Comments VARCHAR2(40),
constraint pk_uniqueid primary key (Uniqueid),
CONSTRAINT fk_agentid
FOREIGN KEY (Agentid)
REFERENCES TableB(Uniqueid
CONSTRAINT fk_teamid
FOREIGN KEY (Teamid)
REFERENCES TableC(Uniqueid)
CONSTRAINT fk_creatorid
FOREIGN KEY (Creatorid)
REFERENCES TableB(Uniqueid)
);
INSERT INTO TableA VALUES (123456, 1, team1, person1, 1, person2, 2, blabla );
CREATE TABLE TableB (
Uniqueid VARCHAR2(40),
Teamid VARCHAR2(40),
Teamleader NUM(2),
constraint pk_uniqueid primary key (Uniqueid),
CONSTRAINT fk_teamid
FOREIGN KEY (Teamid)
REFERENCES TableC(Uniqueid)
);
INSERT INTO TableB VALUES (1, 1, 1);
INSERT INTO TableB VALUES (2, 1, 0);
INSERT INTO TableB VALUES (3, 2, 1);
INSERT INTO TableB VALUES (4, 2, 0);
INSERT INTO TableB VALUES (5, 3, 0);
CREATE TABLE TableC (
Uniqueid VARCHAR2(40),
Teamname VARCHAR2(40)
constraint pk_uniqueid primary key (Uniqueid));
INSERT INTO TableC VALUES (1, team1);
INSERT INTO TableC VALUES (2, team2);
INSERT INTO TableC VALUES (3, team3);
Hop this is ok.
|
|
|
|
|
Re: problem with trigger [message #341038 is a reply to message #341030] |
Fri, 15 August 2008 12:21   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> CREATE TABLE TableC (
2 Uniqueid VARCHAR2(40),
3 Teamname VARCHAR2(40)
4 constraint pk_uniqueid primary key (Uniqueid));
constraint pk_uniqueid primary key (Uniqueid))
*
ERROR at line 4:
ORA-00907: missing right parenthesis
SQL> CREATE TABLE TableC (
2 Uniqueid VARCHAR2(40),
3 Teamname VARCHAR2(40),
4 constraint pk_uniqueid primary key (Uniqueid));
Table created.
SQL> CREATE TABLE TableB (
2 Uniqueid VARCHAR2(40),
3 Teamid VARCHAR2(40),
4 Teamleader NUM(2),
5 constraint pk_uniqueid primary key (Uniqueid),
6 CONSTRAINT fk_teamid
7 FOREIGN KEY (Teamid)
8 REFERENCES TableC(Uniqueid)
9 );
Teamleader NUM(2),
*
ERROR at line 4:
ORA-00907: missing right parenthesis
SQL> CREATE TABLE TableB (
2 Uniqueid VARCHAR2(40),
3 Teamid VARCHAR2(40),
4 Teamleader NUMBER(2),
5 constraint pk_uniqueid primary key (Uniqueid),
6 CONSTRAINT fk_teamid
7 FOREIGN KEY (Teamid)
8 REFERENCES TableC(Uniqueid)
9 );
constraint pk_uniqueid primary key (Uniqueid),
*
ERROR at line 5:
ORA-02264: name already used by an existing constraint
SQL> CREATE TABLE TableB (
2 Uniqueid VARCHAR2(40),
3 Teamid VARCHAR2(40),
4 Teamleader NUMBER(2),
5 constraint pkb_uniqueid primary key (Uniqueid),
6 CONSTRAINT fkb_teamid
7 FOREIGN KEY (Teamid)
8 REFERENCES TableC(Uniqueid)
9 );
Table created.
SQL> CREATE TABLE TableA (
2 Uniqueid VARCHAR2(40),
3 Teamid VARCHAR2(40),
4 Teamname VARCHAR2(40),
5 Creatorname VARCHAR2(40),
6 Creatorid VARCHAR2(40),
7 Agentname VARCHAR2(40),
8 Agentid VARCHAR2(40),
9 Comments VARCHAR2(40),
10 constraint pkc_uniqueid primary key (Uniqueid),
11 CONSTRAINT fkc_agentid
12 FOREIGN KEY (Agentid)
13 REFERENCES TableB(Uniqueid),
14 CONSTRAINT fkc_teamid
15 FOREIGN KEY (Teamid)
16 REFERENCES TableC(Uniqueid),
17 CONSTRAINT fkc_creatorid
18 FOREIGN KEY (Creatorid)
19 REFERENCES TableB(Uniqueid)
20 );
Table created.
SQL> CREATE Trigger activities
2 BEFORE INSERT OR UPDATE on TableA
3 FOR EACH ROW
4
5 DECLARE
6 numb number(2);
7 asteamid varchar2(40);
8 asteamname varchar2(40);
9 agtid varchar2(40);
10 agtname varchar2(40);
11 tid varchar2(40);
12 tname varchar2(40);
13 BEGIN
14
15 If :new.agentid is null or :new.agentname is null then
16 asteamid := :new.teamid;
17 asteamname := :new.teamname;
18 :new.comments := :new.teamid;
19
20
21 Select COUNT(*) INTO numb FROM TableB where teamid=asteamid and teamleader='1';
22
23 :new.outcome := numb;
24
25 if numb >= 1 then
26
27 SELECT agentname, netgainid INTO agtname, agtid
28 FROM TableB WHERE teamid=asteamid and teamleader='1' and rownum<=1;
29
30 :new.agentid := agtid;
31 :new.agentname := agtname;
32
33 END If;
34
35 If numb = 0 then
36 agtid := :new.creatorid;
37 agtname := :new.creatorname;
38
39 SELECT ag.teamid, t.teamname INTO tid, tname FROM TableB ag, TableC t
40 WHERE ag.netgainid=agtid AND ag.teamid=t.netgainid;
41
42 :new.teamid := tid;
43 :new.teamname := tname;
44 :new.agentid := agtid;
45 :new.agentname := agtname;
46 END If;
47
48 END If;
49 END activities;
50 /
Warning: Trigger created with compilation errors.
SQL> sho err
Errors for TRIGGER ACTIVITIES:
LINE/COL
---------------------------------------------------------------------------------
ERROR
-----------------------------------------------------------------------------------------------
19/1
PLS-00049: bad bind variable 'NEW.OUTCOME'
There are many errors and no outcome column.
Regards
Michel
|
|
|
Re: problem with trigger [message #341301 is a reply to message #341038] |
Mon, 18 August 2008 04:29   |
s_ali_hassan
Messages: 18 Registered: August 2008
|
Junior Member |
|
|
i have tried the following, and it creates the 3 tables and puts the sample values in:
CREATE TABLE TableC (
Uniqueid VARCHAR2(40),
Teamname VARCHAR2(40),
primary key (Uniqueid));
CREATE TABLE TableB (
Uniqueid VARCHAR2(40),
Teamid VARCHAR2(40),
Teamleader NUMBER(2),
Agentname VARCHAR2(40),
primary key (Uniqueid),
FOREIGN KEY (Teamid)
REFERENCES TableC(Uniqueid)
);
CREATE TABLE TableA (
Uniqueid VARCHAR2(40),
Teamid VARCHAR2(40),
Teamname VARCHAR2(40),
Creatorname VARCHAR2(40),
Creatorid VARCHAR2(40),
Agentname VARCHAR2(40),
Agentid VARCHAR2(40),
Comments VARCHAR2(40),
Outcome VARCHAR2(40),
primary key (Uniqueid),
FOREIGN KEY (Agentid)
REFERENCES TableB(Uniqueid),
FOREIGN KEY (Teamid)
REFERENCES TableC(Uniqueid),
FOREIGN KEY (Creatorid)
REFERENCES TableB(Uniqueid)
);
INSERT INTO TableC VALUES ('1', 'team1');
INSERT INTO TableC VALUES ('2', 'team2');
INSERT INTO TableC VALUES ('3', 'team3');
INSERT INTO TableB VALUES ('1', '1', '1', 'person1');
INSERT INTO TableB VALUES ('2', '1', '0', 'person2');
INSERT INTO TableB VALUES ('3', '2', '1', 'person3');
INSERT INTO TableB VALUES ('4', '2', '0', 'person4');
INSERT INTO TableB VALUES ('5', '3', '0', 'person5');
INSERT INTO TableA VALUES ('123456', '1', 'team1', 'person1', '1', 'person2', '2', 'blabla', 'outcome1');
CREATE Trigger forum
BEFORE INSERT OR UPDATE on TableA
FOR EACH ROW
DECLARE
numb number(2);
asteamid varchar2(40);
asteamname varchar2(40);
agtid varchar2(40);
agtname varchar2(40);
tid varchar2(40);
tname varchar2(40);
BEGIN
If :new.agentid is null or :new.agentname is null then
asteamid := :new.teamid;
asteamname := :new.teamname;
:new.comments := :new.teamid;
Select COUNT(*) INTO numb FROM TableB where teamid=asteamid and teamleader='1';
:new.outcome := numb;
if numb >= 1 then
SELECT agentname, uniqueid INTO agtname, agtid
FROM TableB WHERE teamid=asteamid and teamleader='1' and rownum<=1;
:new.agentid := agtid;
:new.agentname := agtname;
END If;
If numb = 0 then
agtid := :new.creatorid;
agtname := :new.creatorname;
SELECT ag.teamid, t.teamname INTO tid, tname FROM TableB ag, TableC t
WHERE ag.uniqueid=agtid AND ag.teamid=t.uniqueid;
:new.teamid := tid;
:new.teamname := tname;
:new.agentid := agtid;
:new.agentname := agtname;
END If;
END If;
END forum;
Thanks for reminding me about the outcome column. I forgot about that one.
Thanks for all the help so far - i appreciate all your responses in trying to find a solution to this problem.
[Updated on: Mon, 18 August 2008 04:38] Report message to a moderator
|
|
|
|
Re: problem with trigger [message #341321 is a reply to message #341306] |
Mon, 18 August 2008 06:25   |
s_ali_hassan
Messages: 18 Registered: August 2008
|
Junior Member |
|
|
i tried the following after creating the trigger:
INSERT INTO TableA VALUES ('999', '1', 'team1', 'person3', '3', '', '2', 'blabla', 'outcome1') (Agent id is blank so the trigger should fire).
This yielded the correct result for some reason.
Now i am baffled as it seems the trigger is working perfectly for the tables created.
I am left to conclude that it must be a problem with the wider database tables and/ or other triggers as the database that i have is very large and has all kinds of constarints etc on it.
Thanks for all your help.
|
|
|
|
|
|
Re: problem with trigger [message #341921 is a reply to message #340846] |
Wed, 20 August 2008 14:49  |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
You also don't need all the temp variables, try something like this
CREATE Trigger activities
BEFORE INSERT OR UPDATE on TableA
FOR EACH ROW
BEGIN
If :new.agentid is null or :new.agentname is null then
asteamid := :new.teamid;
asteamname := :new.teamname;
:new.comments := :new.teamid;
Select COUNT(*)
INTO :new.outcome
FROM TableB
where teamid=:new.teamid
and teamleader='1';
if :new.outcome >= 1 then
SELECT agentname, netgainid
INTO :new.agentid, :new.agentname
FROM TableB
WHERE teamid=:new.teamid
and teamleader='1'
and ROWNUM =1;
ELSE
SELECT ag.teamid, t.teamname
INTO :new.teamid, :new.teamname
FROM TableB ag, TableC t
WHERE ag.netgainid=:new.creatorid
AND ag.teamid=t.netgainid;
:new.agentid := :new.creatorid;
:new.agentname := :new.creatorname;
END If;
END If;
END activities;
|
|
|
Goto Forum:
Current Time: Wed Feb 19 02:43:16 CST 2025
|