Link to home
Start Free TrialLog in
Avatar of vijaypatel1
vijaypatel1

asked on

I am getting PLS-00049: bad bind variable 'NEW.DB_INSTANCE_CONTACT_SEQ

I am creating the trigger to insert the record in the Instace_contact table when instance is created.  I am getting PLS-00049: bad bind variable 'NEW.DB_INSTANCE_CONTACT_SEQ error
CREATE OR REPLACE TRIGGER DBA_MGMT.ADD_INSTANCE_CONTACT 
AFTER INSERT Or update on DBA_MGMT.DB_INSTANCE 
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
BEGIN
INSERT INTO DB_INSTANCE_CONTACT
(DB_INSTANCE_CONTACT_ID, 
DB_INSTANCE_ID         
VALUES ( :NEW.DB_INSTANCE_CONTACT_SEQ.NEXTVAL, 
:NEW.db_instance_id );

Open in new window

Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image

Try this.
CREATE OR REPLACE TRIGGER DBA_MGMT.ADD_INSTANCE_CONTACT
AFTER INSERT OR UPDATE ON DBA_MGMT.DB_INSTANCE
REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW
BEGIN
INSERT INTO DBA_MGMT.DB_INSTANCE_CONTACT
      (DB_INSTANCE_CONTACT_ID, DB_INSTANCE_ID
VALUES(DBA_MGMT.DB_INSTANCE_CONTACT_SEQ.NEXTVAL, :NEW.DB_INSTANCE_ID);
END ADD_INSTANCE_CONTACT;
/

Open in new window

Avatar of vijaypatel1
vijaypatel1

ASKER


Warning: Trigger created with compilation errors.

SQL> show error
Errors for TRIGGER DBA_MGMT.ADD_INSTANCE_CONTACT:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/1      PL/SQL: SQL Statement ignored
3/47     PL/SQL: ORA-00917: missing comma
SQL>
I am getting the above said errors when I run your code as it is.
Oops! Typo there. Try this correction.
CREATE OR REPLACE TRIGGER DBA_MGMT.ADD_INSTANCE_CONTACT
AFTER INSERT OR UPDATE ON DBA_MGMT.DB_INSTANCE
FOR EACH ROW
BEGIN
INSERT INTO DBA_MGMT.DB_INSTANCE_CONTACT
      (DB_INSTANCE_CONTACT_ID, DB_INSTANCE_ID)
VALUES(DBA_MGMT.DB_INSTANCE_CONTACT_SEQ.NEXTVAL, :NEW.DB_INSTANCE_ID);
END ADD_INSTANCE_CONTACT;
/

Open in new window

Thanks for your help Mr kamdar..
ASKER CERTIFIED SOLUTION
Avatar of Jinesh Kamdar
Jinesh Kamdar
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
It was the perfect solution