Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Triggers.....

Author  Topic 

YamiFox
Starting Member

1 Post

Posted - 2013-04-21 : 18:22:14
okay so I have the following code:
---drop tables to ensure there aren't errors making this one
Drop Table Student;
Drop Table Course;
Drop Table Faculty;
Drop Table Offering;
Drop Table Enrollment;

----Creation of tables
---Create Student Table
CREATE Table Student (
Stu_ID int constraint Student_Stu_ID_PK Primary Key,
Stu_FN varchar2(50),
Stu_LN varchar2(50),
Stu_City varchar2(25),
Stu_State varchar2(2),
Stu_Zip int,
Stu_Major varchar2(4),
Stu_Class varchar2(50),
Stu_GPA number(3,1),
Stu_Balance number(7,2);

---Create Course Table
CREATE Table Course (
Cou_No varchar2(10) constraint Course_Cou_ID_PK Primary Key,
Cou_Desc varchar2(50),
Cou_Cred Int);

---Create Faculty Table
CREATE Table Faculty (
Fac_ID Int constraint Faculty_Fac_ID_PK Primary Key,
Fac_FN varchar2(50),
Fac_LN varchar2(50),
Fac_Dept varchar2(4),
Fac_Rank varchar2(4),
Fac_HireDate Date,
Fac_Salary number(6),
Fac_Supervisor number(4));

---Create Offering Table
CREATE Table Offering (
Off_No Int constraint Offering_Off_ID_PK Primary Key,
Cou_No varchar2(10),
Off_Term varchar2(7),
Off_Year int,
Off_Loca varchar2(5),
Off_Time varchar2(10),
Off_Day varchar2(5),
Fac_ID int);

---Create Enrollment Table
CREATE Table Enrollment (
Stu_ID int,
Off_No int,
Enr_Grade varchar2(1),
Constraint Enrollment_PK Primary Key(Stu_ID, Off_No));


----Inserting Records into created tables
----Insert Records into Student
INSERT INTO Student (Stu_ID, Stu_FN, Stu_LN, Stu_City, Stu_State, Stu_Zip, Stu_Major, Stu_Class, Stu_GPA) VALUES (101, 'Joe', 'Smith', 'Eau Clare', 'WI', 18121, 'IS', 'FR', 3.0);
INSERT INTO Student (Stu_ID, Stu_FN, Stu_LN, Stu_City, Stu_State, Stu_Zip, Stu_Major, Stu_Class, Stu_GPA) VALUES (102, 'Jenny', 'Sneider', 'Eau Clare', 'WI', 98011, 'IS', 'JR', 3.2);
INSERT INTO Student (Stu_ID, Stu_FN, Stu_LN, Stu_City, Stu_State, Stu_Zip, Stu_Major, Stu_Class, Stu_GPA) VALUES (103, 'Dan', 'Robinson', 'Sartell', 'MN', 98042, 'IS', 'JR', 3.5);
INSERT INTO Student (Stu_ID, Stu_FN, Stu_LN, Stu_City, Stu_State, Stu_Zip, Stu_Major, Stu_Class, Stu_GPA) VALUES (104, 'Sue', 'Williams', 'St. Cloud', 'MN', 56301, 'ACCT', 'SR', 3.2);

---Insert Records into course
INSERT INTO course (Cou_No, Cou_Desc, Cou_Cred) VALUES ('IS 250', 'Application Program Dev. I', 3);
INSERT INTO course (Cou_No, Cou_Desc, Cou_Cred) VALUES ('IS 251', 'Application Program Dev. II', 3);
INSERT INTO course (Cou_No, Cou_Desc, Cou_Cred) VALUES ('IS 445', 'Application Program Dev. III', 3);
INSERT INTO course (Cou_No, Cou_Desc, Cou_Cred) VALUES ('IS 350', 'Systems Analysis and Design I', 3);
INSERT INTO course (Cou_No, Cou_Desc, Cou_Cred) VALUES ('IS 351', 'Systems Analysis and Design II', 3);
INSERT INTO course (Cou_No, Cou_Desc, Cou_Cred) VALUES ('ACCT 291', 'Accounting Principles II', 3);
INSERT INTO course (Cou_No, Cou_Desc, Cou_Cred) VALUES ('IS 443', 'Database Design', 3);

---Insert Records into Faculty
INSERT INTO Faculty (Fac_ID, Fac_FN, Fac_LN, Fac_Dept, Fac_Rank, Fac_HireDate, Fac_Salary, Fac_Supervisor) VALUES (9001, 'Leonard', 'Vince', 'IS', 'ASST', To_Date('12041997', 'DD-MM-YYYY'), 67000, 9003);
INSERT INTO Faculty (Fac_ID, Fac_FN, Fac_LN, Fac_Dept, Fac_Rank, Fac_HireDate, Fac_Salary, Fac_Supervisor) VALUES (9002, 'Victor', 'Fibon', 'IS','ASSO', To_Date('08081999', 'DD-MM-YYYY'), 70000, 9003);
INSERT INTO Faculty (Fac_ID, Fac_FN, Fac_LN, Fac_Dept, Fac_Rank, Fac_HireDate, Fac_Salary, Fac_Supervisor) VALUES (9003, 'Nicki', 'Colan', 'IS', 'PROF', To_Date('20081981', 'DD-MM-YYYY'), 75000, 9010);
INSERT INTO Faculty (Fac_ID, Fac_FN, Fac_LN, Fac_Dept, Fac_Rank, Fac_HireDate, Fac_Salary, Fac_Supervisor) VALUES (9004, 'Fred', 'Wells', 'ACCT', 'ASST', To_Date('28081996', 'DD-MM-YYYY'), 60000, 9010);


---Insert Records into Offering
INSERT INTO Offering (Off_No, Cou_No, Off_Term, Off_Year, Off_Loca, Off_Time, Off_Day, Fac_ID) VALUES (2201, 'IS 250', 'Spring', 2000, 'BB260', '10:30am', 'MWF', 9002);
INSERT INTO Offering (Off_No, Cou_No, Off_Term, Off_Year, Off_Loca, Off_Time, Off_Day, Fac_ID) VALUES (2202, 'IS 250', 'Spring', 1999, 'BB118', '8:00am', 'TTH', 9002);
INSERT INTO Offering (Off_No, Cou_No, Off_Term, Off_Year, Off_Loca, Off_Time, Off_Day, Fac_ID) VALUES (2203, 'IS 350', 'Fall', 2001, 'BB260', '9:30am', 'TTH', 9001);
INSERT INTO Offering (Off_No, Cou_No, Off_Term, Off_Year, Off_Loca, Off_Time, Off_Day, Fac_ID) VALUES (2204, 'IS 351', 'Fall', 2001, 'BB315', '12:30pm', 'TTH', 9003);
INSERT INTO Offering (Off_No, Cou_No, Off_Term, Off_Year, Off_Loca, Off_Time, Off_Day, Fac_ID) VALUES (1101, 'ACCT 291', 'Fall', 2000, 'BB320', '12:30pm', 'MWF', 9010);
INSERT INTO Offering (Off_No, Cou_No, Off_Term, Off_Year, Off_Loca, Off_Time, Off_Day, Fac_ID) VALUES (2205, 'IS 443', 'Fall', 2002, 'BB216', '12:30pm', 'MWF', 9003);

---Insert Records into Enrollment
INSERT INTO Enrollment (Stu_ID, Off_No, Enr_Grade) VALUES (101, 2201, 'A');
INSERT INTO Enrollment (Stu_ID, Off_No, Enr_Grade) VALUES (101, 2203, 'B');
INSERT INTO Enrollment (Stu_ID, Off_No, Enr_Grade) VALUES (102, 2203, 'C');
INSERT INTO Enrollment (Stu_ID, Off_No, Enr_Grade) VALUES (103, 2203, 'B');
INSERT INTO Enrollment (Stu_ID, Off_No, Enr_Grade) VALUES (103, 2201, 'C');
INSERT INTO Enrollment (Stu_ID, Off_No, Enr_Grade) VALUES (103, 1101, 'B');

I need to do the following triggers that i am having a brain fart with:
#1. The maximum number of students per class allowed is 30
#2. Prevent a student from registering for a class with an unpaid balance of more then 500.
#3.a student is not allowed to register for IS 443 unless they completed IS 350 & IS 250.

The code was the code i am supposed to add a triggers to for these events for an assignment that i am frustrated with because of this damn ambiguous teacher and his ineffective explanations.....PLEASE HELP ME WITH THIS...

There one thing that will always be true there will never be a short supply of stupid people in this world

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-22 : 00:51:58
I think you are using Oracle...
we can provide solutions in SQL Server...
TRIGGERS and PL/SQL are somewhat different in Oracle compared to SQL Server...
So its better to post Oracle queries in dbforums.com for quick responses

Refer this link for Triggers in Oracle:
http://docs.oracle.com/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm
--
Chandu
Go to Top of Page
   

- Advertisement -