Home » SQL & PL/SQL » SQL & PL/SQL » Database Trigger
Database Trigger [message #204562] Tue, 21 November 2006 04:54 Go to next message
namrata_bhise
Messages: 18
Registered: October 2006
Location: mumbai
Junior Member
Hi,

Plz help to solve my problem. I had created the following tables

create table new_dept AS
SELECT d.depart_id, d.department_name,d.location_id,c.fname,c.sal from dept d,cms c

create table new_cms as
SELECT fname, lname, empno, sal from cms;

create view cmsview AS
SELECT d.depart_id, d.department_name,d.location_id,c.fname,c.sal from dept d,cms c

Then I created instead Trigger like :
CREATE OR REPLACE TRIGGER new_cms_dept
INSTEAD OF INSERT OR UPDATE OR DELETE ON cmsview
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO new_cms
VALUES (:NEW.fname, :NEW.lname, :NEW.empno, :NEW.sal);
UPDATE new_dept
SET sal = sal+ :NEW.sal
WHERE depart_id = :NEW. depart_id;
ELSEF DELETING THEN
DELETE FROM new_cms
WHERE empno = :OLD.empno;
END IF;
END;

But it gives the error like
PLS-00049: bad bind variable 'NEW.LNAME'
PLS-00049: bad bind variable 'NEW.EMPNO'
PL/SQL: SQL Statement ignored
PLS-00049: bad bind variable 'NEW.'
PL/SQL: ORA-00933: SQL command not properly ended
PLS-00103: Encountered the symbol "DELETING" when expecting one
of the following:
:= . ( @ % ;

PLS-00049: bad bind variable 'OLD.EMPNO'
PLS-00103: Encountered the symbol ";" when expecting one of the

ERROR
-----------------------------------------------------------------
following:
if

Re: Database Trigger [message #204566 is a reply to message #204562] Tue, 21 November 2006 05:14 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your create table scripts don't run, as we don't have DEPT and CMS to build them from, but when I run this script, everything works just fine:

create table new_dept (depart_id number, department_name varchar2(30), location_id number ,fname varchar2(30) ,sal number);

create table new_cms  ( fname varchar2(30), lname varchar2(30) , empno number, sal number);

create view cmsview AS
SELECT d.depart_id, d.department_name,d.location_id,c.fname,c.sal from new_dept d,new_cms c;


CREATE OR REPLACE TRIGGER new_cms_dept
INSTEAD OF INSERT OR UPDATE OR DELETE ON cmsview
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO new_cms
VALUES (:NEW.fname, :NEW.lname, :NEW.empno, :NEW.sal);
UPDATE new_dept
SET sal = sal+ :NEW.sal
WHERE depart_id = :NEW. depart_id;
ELSEF DELETING THEN
DELETE FROM new_cms
WHERE empno = :OLD.empno;
END IF;
END;
Re: Database Trigger [message #204572 is a reply to message #204566] Tue, 21 November 2006 05:30 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I have no idea why it is working for you.
But I got same errors.

I think this is due to

Quote:

create view cmsview AS
SELECT d.depart_id, d.department_name,d.location_id,c.fname,c.sal from dept d,cms c



lname and empno have not been selected in the view cmsview.


Also "PLS-00103: Encountered the symbol "DELETING" when expecting one" is coming because of the typo in ELSIF. Cross check it.

By
Vamsi.

[Updated on: Tue, 21 November 2006 05:32]

Report message to a moderator

Re: Database Trigger [message #204580 is a reply to message #204566] Tue, 21 November 2006 05:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
My mistake - SQL Developer doesn't show the errors when you compile objects in an SQL window.

You are entirely correct - the Lname and Empno columns are not defined in the view CMSVIEW.

There is also an erroneous extra space in this line
:NEW. depart_id;
- it should be
:NEW.depart_id;
, and this
ELSEF DELETING THEN
should be
ELSIF DELETING THEN
Re: Database Trigger [message #204585 is a reply to message #204580] Tue, 21 November 2006 06:02 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
I haven't tested whether Oracle does mind the spaces before / after the '.' or not in that scenario.

But it is working for the following.

select e  . sal from scott .emp e;


By
Vamsi
Re: Database Trigger [message #204587 is a reply to message #204585] Tue, 21 November 2006 06:05 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I get the following error when you include a space after the .:
PLS-00049: bad bind variable 'NEW.'


It doesn't matter whether the space is before or after the ., it seems to make the bind variable invalid.
Re: Database Trigger [message #204591 is a reply to message #204587] Tue, 21 November 2006 06:13 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Understand. Cool
So, Is it only for bind variables?
Will it work for schemas and tables / objects?

By
Vamsi
Re: Database Trigger [message #204644 is a reply to message #204591] Tue, 21 November 2006 08:37 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It looks like it. I haven't given it a thorough test, but it looks like the parser strips out stray spaces in qualified names.

Re: Database Trigger [message #204758 is a reply to message #204644] Wed, 22 November 2006 00:11 Go to previous messageGo to next message
namrata_bhise
Messages: 18
Registered: October 2006
Location: mumbai
Junior Member

Hi all,
Thanks JRowbottom for helping me to solve the problem I prepared the view again like:
create view cmsview AS
SELECT d.depart_id, d.department_name,d.location_id,c.fname,c.lname,c.empno,c.sal from dept d,cms c

and again created the Trigger like:
CREATE OR REPLACE TRIGGER new_cms_dept
INSTEAD OF INSERT OR UPDATE OR DELETE ON cmsview
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO new_cms
VALUES (:NEW.fname, :NEW.lname, :NEW.empno, :NEW.sal);
UPDATE new_dept
SET sal = sal+ :NEW.sal
WHERE depart_id = :NEW. depart_id;
ELSEF DELETING THEN
DELETE FROM new_cms
WHERE empno = :OLD.empno;
END IF;
END;

It gives the message Trigger Created.

Many Thanks to all of you.
Re: Database Trigger [message #204789 is a reply to message #204758] Wed, 22 November 2006 02:19 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It was @vamsi kasina who spotted the problem first.
Re: Database Trigger [message #204794 is a reply to message #204562] Wed, 22 November 2006 02:28 Go to previous messageGo to next message
kiran9i
Messages: 4
Registered: November 2006
Location: 1
Junior Member

hi namrata
first of all try to remove all syntax error u have
then simply try to insert update and delete from ur view
and u can try for creating trigger
this document will help u


bye
  • Attachment: slides10.pdf
    (Size: 89.59KB, Downloaded 1111 times)
Re: Database Trigger [message #204801 is a reply to message #204794] Wed, 22 November 2006 02:46 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I appreciate that reading the posts that have already been made is a terrible chore, but I think this problem has already been solved.
Previous Topic: Aggregate Function
Next Topic: HELP ASAP
Goto Forum:
  


Current Time: Wed Dec 07 18:35:46 CST 2016

Total time taken to generate the page: 0.09553 seconds