Home Sweet Home for your Pets !

dennisooi — Mon, 02 Jan 2012 19:27:59 +0800

The new concept of Pets house in malaysia. Home sweet home for your pets.
So, hurry and give your pet a better home today!

More info please visit us at www.chillipets.com or www.facebook.com/chillipets

More image at http://www.chillipets.com/?p=317

Thank you

oracle

dennisooi — Mon, 10 Apr 2006 07:42:30 +0800

really dunno how to solve the mutating error....hope any buddy know can helpme..thank..below is my coding....

The table name: instructor
INSTRUCTOR_ID INSTRUCTOR_NAME SALARY COMMISSION MENTOR_ID DATE_HIRE
------------- --------------- --------- ---------- --------- ---------
700 WAYNE 4500 300 16-MAY-81
628 MONROE 3000 700 16-JUN-84
790 NEWMAN 3100 300 700 16-DEC-82
795 BOGART 3200 700 16-DEC-83
515 SHELLEY 3500 200 700 20-JAN-91
222 CAINE 5500 350 02-NOV-76
978 STEEL 5000 250 222 16-JAN-80
560 LAUREL 5200 200 978 16-DEC-83
835 SPARKS 4000 200 978 16-DEC-84
243 TUCKER 2000 835 18-DEC-90
263 JOHNSON 4000 835 18-JUL-92
453 LODGE 2500 100 835 14-SEP-98

Procedure:
CREATE OR REPLACE PROCEDURE question2_pro
(y_InstructorId IN instructor.instructor_id%TYPE,
y_ServiceYears OUT NUMBER,
y_Salary OUT instructor.salary%TYPE)

IS
BEGIN

select months_between(sysdate, date_hired)/12, salary
into y_ServiceYears, y_Salary from instructor
where instructor_id = y_InstructorId;

END question2_pro;
/

Triggle:
CREATE OR REPLACE TRIGGER question2_tri
AFTER INSERT OR UPDATE OF mentor_id ON instructor
FOR EACH ROW
DECLARE

V_SYears NUMBER;
V_Salary instructor.salary%TYPE;
V_Count NUMBER;
BEGIN
/* This is to check if the new mentor_id is an existing mentor.
If count = 0, means it is not. */
SELECT count(mentor_id) INTO V_Count
FROM instructor
WHERE mentor_id = :new.g_mentor_id;

IF V_Count = 0 THEN
question2_pro(:NEW.mentor_id, V_SYears, V_Salary);

IF (V_SYears <= 20) AND (V_Salary <= 3400) THEN
RAISE_APPLICATION_ERROR (-20202, 'Instructor:' ||
TO_CHAR(:NEW.mentor_id) || ' cannot be a mentor.');
END IF;
END IF;
END;
/

Triggle create to slove the mutation error:

create or replace trigger checkCount
before INSERT OR UPDATE OF mentor_id ON instructor
for each row
begin
new.g_mentor_id := :NEW.mentor_id;
end;

Package create to slove the mutation problem:
create or replace package new as
g_mentor_id number;
end;

Your help wil appreaciate....thank you

oracle

dennisooi — Thu, 06 Apr 2006 14:13:04 +0800

1) Design, create and test trigger(s) to implement the following new 'business rule'

To prevent any changes to the Course table between 6pm and 8am on Weekdays (Mon-Fri), and at weekends.

Explain how this could be made to apply to all the tables in the course database, not just the Course table.
i jus dunno how to explain how to apply to all database table??
any buddy can help me??

thank....

Lowyat.NET: Latest topics by dennisooi

Home Sweet Home for your Pets !

oracle

oracle