Delete Record - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Delete Record

Author

Topic

FabioEinaus
Starting Member

41 Posts

Posted - 2006-11-16 : 12:36:00

Hi ppl!

I have to delete the record that refers to the Tarefa in ProjectosTarefas when I know that the Tarefa that I delete on ProjectoTrabalhos is the last one.

I tried to code that on the Delete Trigger but it doesn't works.

The part of the code that I've made looks like this:

SELECT @TempDel = (SELECT I.Tempo FROM ProjectosTrabalhos P, Deleted I WHERE P.ProjectID = I.ProjectID)

IF @TempDel = @ConsHR
BEGIN
SELECT * INTO ProjectosTarefasCopia FROM ProjectosTarefas P, Deleted I
DELETE ProjectosTarefasCopia
WHERE (P.ProjectID = I.ProjectID AND P.Tarefa = I.Tarefa)
DROP TABLE ProjectosTarefasCopia
END
ELSE
BEGIN
UPDATE ProjectosTarefas SET ProjectosTarefas.Consumido = ProjectosTarefas.Consumido - I.Custo, ProjectosTarefas.ConsumidoHR = @HR FROM Deleted I
WHERE ProjectosTarefas.ProjectID = I.ProjectID AND ProjectosTarefas.Tarefa = I.Tarefa
UPDATE Projectos SET Projectos.ConsumidoHR = @ConsHR
FROM ProjectosTarefas, Deleted I WHERE Projectos.ProjectID = I.ProjectID
END

Thanks

FabioEinaus
Starting Member

41 Posts

Posted - 2006-11-17 : 04:41:00

Anyone?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-17 : 04:48:08

I am not sure what you want? What is @ConsHR? @HR?

Besides, your code doesn't make much sense either.
Firstly you are making copy of deleted records to ProjectosTarefasCopia (with cross join), then you are deleting those records without defining aliases and then you are dropping copy table.

Then why you bother to create table at all?

Please explain in detail what you want with some sample data.

Harsh Athalye
India.
"Nothing is Impossible"

FabioEinaus
Starting Member

41 Posts

Posted - 2006-11-17 : 05:54:07

First of all thanks for the reply.

I'm doing a project manager and when I delete a task in ProjectosTrabalhos that an employe have done, if that task was unique it doesn't make sense let here in the table ProjectosTarefas right?
It can exists the same task on the same projectID for diferent employes or the same employe but on diferent date and the table ProjectosTarefas, records the total of hours that that task was used on that projectID.

What I want is compare if the task done by the employe is unique (or in this case, if the amount of hours of that task is the same of the one in ProjectosTarefas)and if it really is, I want to delete the record on ProjectosTarefas that contains that task..

I'm not sure if you can understand what I want but I tried hard

Cheers

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-17 : 07:17:23

I am not sure how accurate me reply will be without the tables, but anyway I am posting it:

Create Trigger trg1
on ProjectoTrabalhos
for delete
as

If Exists (select * from Deleted t1 Join ProjectosTarefas t2 on t1.ProjectID = t2.ProjectID and t1.TaskID = t2.TaskID and t1.HoursUsed = t2.HoursUsed)
Begin
Delete pt
from ProjectosTarefas pt Join Deleted D
on pt.ProjectID = D.ProjectID and
pt.TaskID = D.TaskID
End
Else
Begin
Update pt
Set HoursUsed = HoursUsed + d.HoursUsed
from ProjectosTarefas pt Join Deleted D
on pt.ProjectID = D.ProjectID and
pt.TaskID = D.TaskID
End
Go

Harsh Athalye
India.
"Nothing is Impossible"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources