Home » SQL & PL/SQL » SQL & PL/SQL » problem with trigger
problem with trigger [message #340846] Thu, 14 August 2008 10:15 Go to next message
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 #340849 is a reply to message #340846] Thu, 14 August 2008 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
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.

How do you know that as numb is never displayed or the like anywhere?

Regards
Michel
Re: problem with trigger [message #340946 is a reply to message #340849] Fri, 15 August 2008 02:43 Go to previous messageGo to next message
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 #340948 is a reply to message #340946] Fri, 15 August 2008 02:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What you say is not correct if we assume the rest of the code is correct.
You said "numb" (actually this is not numb but :new.outcome) is always 0 but not if you omit "if numb=0" part, but this part is only executed if numb = 0 so not in the case when numb is not 0, the case you say you didn't see the correct result.

It is not in the trigger search in another place, I bet you have an "when others then..." somewhere or the like (that is you ignore an error) or another trigger.

By the way, the following lines is useless:
:new.outcome := :new.teamname;
as you reset :new.outcome with numb just after.

Regards
Michel
Re: problem with trigger [message #340956 is a reply to message #340948] Fri, 15 August 2008 03:19 Go to previous messageGo to next message
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 #340964 is a reply to message #340956] Fri, 15 August 2008 06:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is not the same trigger than first one.
I didn't read the post but search the other points I mentioned.

Regards
Michel
Re: problem with trigger [message #340970 is a reply to message #340964] Fri, 15 August 2008 06:12 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #340978 is a reply to message #340976] Fri, 15 August 2008 06:54 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Keep your lines in 80 characters and post valid statements.

Regards
Michel
Re: problem with trigger [message #340982 is a reply to message #340976] Fri, 15 August 2008 07:02 Go to previous messageGo to next message
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 #340988 is a reply to message #340982] Fri, 15 August 2008 07:55 Go to previous messageGo to next message
joy_division
Messages: 4643
Registered: February 2005
Location: East Coast USA
Senior Member
Is this Oracle?
Those are not valid CREATE TABLE or INSERT statements for Oracle.
Re: problem with trigger [message #341002 is a reply to message #340988] Fri, 15 August 2008 08:31 Go to previous messageGo to next message
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 #341004 is a reply to message #340846] Fri, 15 August 2008 08:37 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Quote:

i will amend in a while



Do not hurry on my account because you have qualified for a place on my IGNORE list for knowingly posting bogus & misleading information and wasting volunteer's time.

[Updated on: Fri, 15 August 2008 08:37] by Moderator

Report message to a moderator

Re: problem with trigger [message #341009 is a reply to message #340982] Fri, 15 August 2008 09:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Are they not 80 characters???????

Not they, they were in your previous and I have fixed it.
But as already said, there are still not valid.

Quote:
little busy at the moment.
I will try and amend for oracle

Take your time, we have no problem we are just trying to help you solve yours.

Regards
Michel
Re: problem with trigger [message #341021 is a reply to message #340982] Fri, 15 August 2008 09:33 Go to previous messageGo to next message
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 #341030 is a reply to message #341021] Fri, 15 August 2008 10:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Why don't you try them first yourself?
Strings are enclosed by quotes.
Re: problem with trigger [message #341031 is a reply to message #341021] Fri, 15 August 2008 10:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Insert statements are still not correct, string literals are unclosed between single quotes '.

Regards
Michel
Re: problem with trigger [message #341038 is a reply to message #341030] Fri, 15 August 2008 12:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 #341306 is a reply to message #341301] Mon, 18 August 2008 05:12 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Now explain in your example when and where happens your problem. Show it with an execution. Use SQL*Plus and copy and paste your session.

By the way, do we have to create the trigger AFTER inserting the data as you post it or before?

Regards
Michel
Re: problem with trigger [message #341321 is a reply to message #341306] Mon, 18 August 2008 06:25 Go to previous messageGo to next message
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 #341324 is a reply to message #341321] Mon, 18 August 2008 06:48 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I must confess that that's pretty much exactly the answer I expected.
Re: problem with trigger [message #341325 is a reply to message #341321] Mon, 18 August 2008 06:52 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 15 August 2008 09:55
It is not in the trigger search in another place, I bet you have an "when others then..." somewhere or the like (that is you ignore an error) or another trigger.
Regards
Michel


Re: problem with trigger [message #341390 is a reply to message #341325] Mon, 18 August 2008 12:23 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Sorry, I misread a post; removed my original reply.

[Updated on: Mon, 18 August 2008 12:23]

Report message to a moderator

Re: problem with trigger [message #341921 is a reply to message #340846] Wed, 20 August 2008 14:49 Go to previous message
Bill B
Messages: 1484
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;

Previous Topic: invalid identifier error
Next Topic: Row-wise comparison
Goto Forum:
  


Current Time: Fri Dec 09 08:01:38 CST 2016

Total time taken to generate the page: 0.19240 seconds