?
Solved

Trigger with Sequence and PL/SQL

Posted on 2011-04-21
5
Medium Priority
?
478 Views
Last Modified: 2012-06-27
Hi. I want to create a BEFORE INSERT trigger for a table 'Manager'. I want the trigger to automatically calculate the manager_id value from a sequence.

I've done the following...
CREATE OR REPLACE TRIGGER manager_trig

BEFORE INSERT ON Manager
FOR EACH ROW
WHEN (NEW.manager_id > 0)


And create the sequence.....

CREATE SEQUENCE manager_seq START WITH 1
INCREMENT BY 1;

I also want to also create a PL block that shows the trigger working,  when INSERT,




0
Comment
Question by:graziazi
  • 3
  • 2
5 Comments
 
LVL 5

Accepted Solution

by:
morgulo earned 2000 total points
ID: 35444286
CREATE OR REPLACE TRIGGER manager_trig
BEFORE INSERT ON Manager
FOR EACH ROW
BEGIN
   SELECT manager_seq.nextval INTO :NEW.manager_id FROM dual;
END;

Open in new window

0
 

Author Comment

by:graziazi
ID: 35444363
Thanks morqulo!

I'd also like to create a PL/SQL block where I insert (just using the other colum names)  eg name salary
0
 

Author Comment

by:graziazi
ID: 35444367
i.e demonstrate the trigger working
0
 
LVL 5

Expert Comment

by:morgulo
ID: 35444501
Type "insert into Manager(...) values(...)" without manager_id column.
If you insert row, then select table and see if manager_id has correct value.
0
 

Author Closing Comment

by:graziazi
ID: 35444625
Thanks
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question