need help in rectifying error [message #662877] |
Mon, 15 May 2017 01:15 |
|
sowk
Messages: 11 Registered: March 2017
|
Junior Member |
|
|
I am trying to write a trigger to update the count of 'NOOFVOTERS' of the respective constituency in 'constituency' table
after inserting a tuple into 'voters' table
cid of voters is FK refers CONSTID of constituency
SQL> desc voters;
Name Null? Type
----------------------------------------- -------- ----------------------------
VOTERID NOT NULL NUMBER(38)
NAME VARCHAR2(10)
AGE NUMBER(38)
HOUSENO VARCHAR2(4)
CITY VARCHAR2(10)
STATE VARCHAR2(10)
PINCODE VARCHAR2(6)
CID NUMBER(38)
SQL> desc constituency;
Name Null? Type
----------------------------------------- -------- ----------------------------
CONSTID NOT NULL NUMBER(38)
CONSTNAME VARCHAR2(10)
STATE VARCHAR2(10)
NOOFVOTERS NUMBER(38)
SQL> create or replace trigger
2 noofv
3 after insert or update or delete on voters
4 for each row
5 update constituency
6 set NOOFVOTERS=(select count(VOTERID) from voters
7 group by CID
8 having cid=NEW.cid)
9 where CONSTID=NEW.CONSTID;
10 /
Warning: Trigger created with compilation errors.
I am not getting what is the error in my trigger code
please help
|
|
|
|
|
|
Re: need help in rectifying error [message #662884 is a reply to message #662881] |
Mon, 15 May 2017 02:49 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
The trigger is missing a BEGIN and an END and the new. needs to be :new.
After that it should compile, but it'll throw a mutating table error at run-time as the others already pointed out.
Also that sub-query is unnecessarily complicated, should be:
select count(VOTERID) from voters
where cid=:NEW.cid
|
|
|
Re: need help in rectifying error [message #662890 is a reply to message #662880] |
Mon, 15 May 2017 04:17 |
|
sowk
Messages: 11 Registered: March 2017
|
Junior Member |
|
|
SQL> create table constituency(
2 constid int primary key,
3 constname varchar(10),
4 state varchar(10),
5 noofvoters int);
Table created
SQL> create table voters(
2 voterid int primary key,
3 name varchar(10),
4 age int,
5 houseno varchar(4),
6 city varchar(10),
7 state varchar(10),
8 pincode varchar(6),
9 cid int references constituency(constid));
Table created.
SQL> insert all into constituency values(1,'dk','karnataka',0)
2 into constituency values(2,'chikkodi','karnataka',0)
3 into constituency values(3,'belguaum','karnataka',0)
4 into constituency values(4,'rajkot','gujarat',0)
5 into constituency values(5,'vadodara','gujarat',0)
6 select * from dual;
5 rows created.
SQL> select * from voters;
VOTERID NAME AGE HOUS CITY STATE PINCOD CID
---------- ---------- ---------- ---- ---------- ---------- ------ ----------
11 abc 33 2-7c chikkodi karnataka 577028 1
22 mno 33 2-7c chikkodi karnataka 577028 1
33 xyz 33 2-7c chikkodi karnataka 577028 1
SQL> select * from constituency
2 ;
CONSTID CONSTNAME STATE NOOFVOTERS
---------- ---------- ---------- ----------
1 dk karnataka 3
2 chikkodi karnataka 0
3 belguaum karnataka 0
4 rajkot gujarat 0
5 vadodara gujarat 0
|
|
|
Re: need help in rectifying error [message #662891 is a reply to message #662879] |
Mon, 15 May 2017 04:33 |
|
sowk
Messages: 11 Registered: March 2017
|
Junior Member |
|
|
Thank you for the reply. when I insert new tuple it works. but if i update the old constituency count remain the same how to change old values?
SQL> create or replace trigger
2 noofv
3 after insert or update or delete on voters
4 for each row
5 begin
6 update constituency
7 set NOOFVOTERS= NOOFVOTERS+1
8 where CONSTID=:NEW.cid;
9 end;
10 /
Trigger created.
SQL> select * from constituency;
CONSTID CONSTNAME STATE NOOFVOTERS
---------- ---------- ---------- ----------
1 dk karnataka 3
2 chikkodi karnataka 0
3 belguaum karnataka 0
4 rajkot gujarat 0
5 vadodara gujarat 0
6 supaul bihar 0
7 hajipur bihar 0
7 rows selected.
SQL> select * from voters;
VOTERID NAME AGE HOUS CITY STATE PINCOD CID
---------- ---------- ---------- ---- ---------- ---------- ------ ----------
11 abc 33 2-7c chikkodi karnataka 577028 1
22 mno 33 2-7c chikkodi karnataka 577028 1
33 xyz 33 2-7c chikkodi karnataka 577028 1
SQL> update voters set CID=2;
3 rows updated.
SQL> select * from constituency;
CONSTID CONSTNAME STATE NOOFVOTERS
---------- ---------- ---------- ----------
1 dk karnataka 3
2 chikkodi karnataka 3
3 belguaum karnataka 0
4 rajkot gujarat 0
5 vadodara gujarat 0
6 supaul bihar 0
7 hajipur bihar 0
7 rows selected.
SQL> insert all into voters values(44,'asdf',32,'12','hajipur','bihar','423718',
7)into voters values(55,'lkj',32,'14','hajipur','bihar','423718',7)
2 select * from dual;
2 rows created.
SQL> select * from voters;
VOTERID NAME AGE HOUS CITY STATE PINCOD CID
---------- ---------- ---------- ---- ---------- ---------- ------ ----------
11 abc 33 2-7c chikkodi karnataka 577028 2
22 mno 33 2-7c chikkodi karnataka 577028 2
33 xyz 33 2-7c chikkodi karnataka 577028 2
44 asdf 32 12 hajipur bihar 423718 7
55 lkj 32 14 hajipur bihar 423718 7
SQL> select * from constituency;
CONSTID CONSTNAME STATE NOOFVOTERS
---------- ---------- ---------- ----------
1 dk karnataka 3
2 chikkodi karnataka 3
3 belguaum karnataka 0
4 rajkot gujarat 0
5 vadodara gujarat 0
6 supaul bihar 0
7 hajipur bihar 2
7 rows selected.
|
|
|
Re: need help in rectifying error [message #662892 is a reply to message #662891] |
Mon, 15 May 2017 04:43 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
With an update statement that uses :old.cid in the where clause.
There examples in the thread Michel linked to. There's also an explanation, with examples, of why you shouldn't do this.
|
|
|
|
|
|
Re: need help in rectifying error [message #663099 is a reply to message #662917] |
Mon, 22 May 2017 07:35 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
never store a computed value that is easily gotten via a simple query. It WILL become incorrect. simply use
select a.constid,a.constname,a.state,sum(1) noofvoters
from voters b, constituency a
where a.constid = b.cid
group by a.constid,a.constname,a.state
order by a.constid;
|
|
|