Home » SQL & PL/SQL » SQL & PL/SQL » need help in rectifying error (sql,oracle 11g)
need help in rectifying error [message #662877] Mon, 15 May 2017 01:15 Go to next message
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 #662879 is a reply to message #662877] Mon, 15 May 2017 01:20 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
A row trigger cannot query the table on which it is defined: this is logically impossible.

[Updated on: Mon, 15 May 2017 01:20]

Report message to a moderator

Re: need help in rectifying error [message #662880 is a reply to message #662877] Mon, 15 May 2017 01:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

If you post a working Test case: create table statements then we will work with your table and data and reproduce what you have to see where the error is.

Have a look at SHOW ERRORS command to locate these ones.


Re: need help in rectifying error [message #662881 is a reply to message #662877] Mon, 15 May 2017 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

In addition, storing a computed value in a column is a bad idea which will not work.
Read Trigger-summing values topic.

Re: need help in rectifying error [message #662884 is a reply to message #662881] Mon, 15 May 2017 02:49 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
cookiemonster
Messages: 13917
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 #662894 is a reply to message #662892] Mon, 15 May 2017 05:26 Go to previous messageGo to next message
sowk
Messages: 11
Registered: March 2017
Junior Member
thank you this trigger I wrote. it works now.

SQL> create or replace trigger
  2     noofvinsert
  3       after insert  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> create or replace trigger
  2     noofvupdate
  3       after update on voters
  4        for each row
  5     begin
  6       update constituency
  7       set NOOFVOTERS=NOOFVOTERS+1
  8      where CONSTID=:NEW.Cid;
  9  update constituency
 10       set NOOFVOTERS=NOOFVOTERS-1
 11      where CONSTID=:OLD.Cid;
 12  end;
 13     /

Trigger created.

SQL> create or replace trigger
  2     noofvdelete
  3       after delete  on voters
  4        for each row
  5     begin
  6       update constituency
  7       set NOOFVOTERS=NOOFVOTERS-1
  8      where CONSTID=:old.Cid;
  9  end;
 10     /

Trigger created.
Re: need help in rectifying error [message #662902 is a reply to message #662894] Mon, 15 May 2017 07:09 Go to previous messageGo to next message
EdStevens
Messages: 1376
Registered: September 2013
Senior Member
sowk wrote on Mon, 15 May 2017 05:26
thank you this trigger I wrote. it works now.

And yet you have been told several time why, for a variety of reasons, this is a fundamentally flawed design. In my mind, the very worst flaw is the fact that (as previously stated) it is a fundamental design flaw to store values that can (and should be) computed from other values that are already stored.

So any time you would want to query constituency.noofvoters, what you SHOULD be doing is select count(*) from voters where cid=<whatever>. You could even bake that into a view.

But your current process of ignoring all the advice being given is like driving down the road, passing signs that say "Bridge Out Ahead", and you just keep asking how to make your car go faster down that road.
Re: need help in rectifying error [message #662917 is a reply to message #662902] Mon, 15 May 2017 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 68624
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

+1 (once again).

Re: need help in rectifying error [message #663099 is a reply to message #662917] Mon, 22 May 2017 07:35 Go to previous message
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;
Previous Topic: Working of stored procedure
Next Topic: Oracle Sequences
Goto Forum:
  


Current Time: Thu Mar 28 11:04:48 CDT 2024