SQL Trigger Help - SQL Server Forums

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

SQL Trigger Help

Author

Topic

young_padawan
Starting Member

2 Posts

Posted - 2011-05-14 : 12:01:24

I have gotten pretty far on this on my own. All I am asking for is help with the actual deleting of the values in my trigger. I am using Oracle SQL Developer.

So I have these two very basic tables.

CODE PNAME
------------ --------------
101 ProjectX
222 ProjectY
355 ProjectZ
973 ProjectAlpha

ID NAME PCODE HOURS
----- ------------ -------------- ------------
55055 Smith 101 20
55055 Smith 222 10
39002 Bob 973 25
00001 Preston 355 5
10000 Logan 355 5
00777 Bond 222 20

When an employee is removed from the second table and leaves no employee working on any project we should remove that project from the first table.

So for example if I delete job 355 nothing happens because one person is still working on that project.

Now when I delete ProjCode 101 or update ProjCode 101 to some other existing project I should remove this Code/projName from First table.

Then the tables will look as such and project 101 is deleted.

CODE NAME
------------ --------------
222 ProjectY
355 ProjectZ
973 ProjectAlpha

ID NAME PROJCODE HOURS
----- ------------ -------------- ------------
55055 Smith 973 20
55055 Smith 222 10
39002 Bob 973 25
10000 Logan 355 5
00777 Bond 222 20

I got the hint part of the problem.
Start with a query that returns the value of projects not being worked on by any person. (Not in the intersect, Oracle uses minus command for this).

--Start my trigger
CREATE OR REPLACE TRIGGER DeleteProject AFTER DELETE ON ASSIGNMENT
FOR EACH ROW
BEGIN

--findvalue to be removed. Example if 101 changed or deleted, that is
--the value the following query will return. Also before any updates -
--or deletes I verified that 0 rows returned for below query.

select count(code) into countCode FROM (SELECT code FROM PROJECT
minus
SELECT ProjCode FROM ASSIGNMENT);

select code FROM Project
minus
SELECT ProjCode FROM ASSIGNMENT;

My real question is how do I use the above to delete 101 from the first table. Here is my two tries that were unsuccessful.

CREATE OR REPLACE TRIGGER DeleteProject AFTER UPDATE or DELETE ON ASSIGNMENT
FOR EACH ROW
Declare
countCode integer;
delCode integer;
pCode integer;
BEGIN
select count(code) into countCode FROM (SELECT code FROM PROJECT
minus
SELECT ProjCode FROM ASSIGNMENT);

select code FROM Project
minus
SELECT ProjCode FROM ASSIGNMENT;

IF (countCode = 0) THEN
DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || delCode);
DBMS_OUTPUT.PUT_LINE('Project to be removed is ' || countCode);
DELETE FROM PROJECT WHERE (delCode = :old.code);
END IF;
END;

I am able to get the print statements to work so the if statement works as expected.

Thank you for your help.

Now back

PS. I was able to write other triggers, procedures and PL/SQL statements, but I am stuck with this last piece.

PPS. I even have the print statement to add to the end of this when it does what I want and will verify by printing out the values in both tables.

PPPS. I am sure it is something stupid I am missing.

Thanks again.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-14 : 12:55:06

This is only a forum for MS SQL Server.
You can get better help at dbforums.com/oracle

No, you're never too old to Yak'n'Roll if you're too young to die.

young_padawan
Starting Member

2 Posts

Posted - 2011-05-14 : 14:27:11

quote:
Originally posted by webfred

This is only a forum for MS SQL Server.
You can get better help at dbforums.com/oracle

No, you're never too old to Yak'n'Roll if you're too young to die.

Thanks. Done.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources