TRIGGER for delete - 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)

TRIGGER for delete

Author

Topic

e.bar
Starting Member

25 Posts

Posted - 2003-04-07 : 16:56:47

Hi!
The TRIGGER below is used for insert data in a second table. I need a TRIGGER for deleting data using the same tables.
Tks.

CREATE TRIGGER Inslancamentos ON dbo.lancamentos
FOR INSERT
AS
declare
@indice int,
@ProcessoID int,
@Parcelas int,
@Vencto datetime,
@Valor decimal(15,2)
select @Parcelas = Parcelas, @Valor = TotalCorrigido/Parcelas+(TotalCorrigido*(0.00072)), @Vencto=PrimVencto, @ProcessoID= ProcessoID from inserted
set @indice = 1
while @indice <= @Parcelas
begin
insert parcelas (ProcessoID, Quota, Valor, Vencto)
values (@ProcessoID, @indice, @Valor, DATEADD(month,@indice-(1), @Vencto))
set @indice= @indice+1
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-04-07 : 17:24:28

Do you want the DELETE trigger to do the same thing as the INSERT trigger, meaning write a record to another table when a DELETE/INSERT happens? You could either modify this trigger so that it handles both cases, or create a new one that just handles the deletes. If you just want to modify this trigger, then change FOR INSERT to FOR INSERT, DELETE. Then you will need to adjust your code to handle both cases. You can use the IF UPDATE(columns) to test whether or not it was an insert (see SQL Server Books Online for specifics about IF UPDATE). Then IF UPDATE is false, then you know it's a delete that happened. You could also use the COLUMNS_UPDATE() function to test whether or not the record was deleted or inserted. SQL Server Books Online has a pretty good example of this.

Tara

e.bar
Starting Member

25 Posts

Posted - 2003-04-07 : 19:23:17

Hello Tara!
I´m still learning SQL and I really need a help.
I prefer create an other TRIGGER that will delete all rows in dbo.parcelas when I delete a record on dbo.lancamentos.
I´m not sure, but I think the TRIGGER for delete would be smaller than the TRIGGER for insert. Am I right? Could help me with it?
Tks.

darinh
Yak Posting Veteran

58 Posts

Posted - 2003-04-07 : 22:26:50

Assuming @ProcessoID is your index something like this should work

 
CREATE TRIGGER Dellancamentos ON dbo.lancamentos 
FOR DELETE
AS 
declare @ProcessoID int

select @ProcessoID= ProcessoID from deleted

delete from parcelas where ProcessoID = @ProcessoID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources