Oralce/SQLserver trigger help???
Any experts in Oracle or SqlServer?
I,m trying to write a trigger, when a new department is added,
• Checks the uniqueness of the DNO
– If the value is unique then it adds the department to the
table
– Otherwise, copies the department info into an audit table
I already created 3 table emp, dept, audittable and stored procedure that checks the uniqueness of Dept no
Below is the code i wrote for trigger, it does checks the uniqueness of Deptno but doesn't copy it to audit table when wrong dept is inserted.
CREATE OR REPLACE TRIGGER trig_dept_new
BEFORE INSERT on DEPT
FOR EACH ROW
DECLARE dept_exist NUMBER;
BEGIN
SELECT Count(DEPTNO) INTO dept_exist
FROM DEPT
WHERE DEPT.DEPTNO = :NEW.DEPTNO;
IF dept_exist >= 1 THEN
INSERT INTO AUDITDEPT (DEPTNO, DNAME, LOC)
values (:NEW.DEPTNO, :NEW.DNAME, :NEW.LOC);
RAISE_APPLICATION_ERROR
(-20685, 'DEPT is not added, copied into audit table');
END IF;
END;
/
SQL> insert into DEPT (DEPTNO, DNAME, LOC) values (20,'SUPPORT','DALLAS');
insert into DEPT (DEPTNO, DNAME, LOC) values (20,'SUPPORT','DALLAS')
*
ERROR at line 1:
ORA-20685: DEPT is not added, copied into audit table
ORA-06512: at "A.TRIG_DEPT_NEW", line 11
ORA-04088: error during execution of trigger 'A.TRIG_DEPT_NEW'
Any help would be appreciated.
. Bhan0001 Looks like there is an error at values (:NEW.DEPTNO, :NEW.DNAME, :NEW.LOC);
ORA-06512 :-
// *Cause: Backtrace message as the stack is unwound by unhandled
// exceptions.
// *Action: Fix the problem causing the exception or write an exception
// handler for this condition. Or you may need to contact your
// application administrator or DBA.
Post it in DBASupport.com,you will get an expert advice.I do it all the time and get immediate replies and they are the best,plus it's free.Sorry bro havent done any triggers lately.Did you disable any constraints ( foreign?) before trying to audit the table?Check the alert log,you will see what exactly is happening.
Thanks bme2005, I just posted my thread out there. Hopefully,I'will get my solution back soon.
BTW, if i take out the error handiling, it inserts it into both the table eventhough the input value dept is already in dept table. It should insert only in auditdept table.
Thanks in advance to everybody who can input some thoughts.
pay freaking attention in class, and hit the books more often.
You have it backward.
As far as I can see you are trying to use a trigger to check if a record exists or not in the DEPT table during insert and then raising an error if the record already exists in the table. This is not the proper way to handle it.
Use a primary key constraint instead on the DEPT Table. Define a primary key constraint on the DeptNo column. Oracle will now automatically disallow the insert of the duplicate records in the table. Integrity constraints like this are best left to the database to handle. How are you going to handle the updates?
I hope you are using stored procedures/views to do the update/insert in the table instead of directly updating/inserting in the tables. As far as I know good practices dictate that the tables should never be accessed directly and indeed most of the good DBAs disallow direct access to the table (this is easy to secure). If you are using a stored procedure, then you can trap the constraint violation (the error code escapes my mind offhand right now) in an exception block and insert the record in the audit table.
PS I suggest you grab a good book on relational database design. Try to get Joe Celko's books. Looking at the code (i mean no offense to you personally), the database can do with a revision.
Cheers
Brat
Please log in to reply to this post
You can also log in using your Facebook
