Trigger that calculates and updates age

Question

Trigger that calculates and updates age

Asked 9 years, 8 months ago

Viewed 891 times

3

I want to create a Rigger that when firing calculates the customer’s age on each table row and updates itself.

I tried it this way:

create trigger Atualiza_Idade
on cliente
for insert,update,delete
as 

declare
@i int,
@dtnasc  datetime ,
@Idade int,
@Hoje DATETIME,
@Condicao int
Set @i=1
Set @Hoje=(SELECT GETDATE ( ))

while (@i<(select COUNT( *) from Cliente ))
begin
select @dtnasc=DataNasimento,@Idade=Idade from Cliente where id=@i;


set @Condicao = (SELECT FLOOR(DATEDIFF(DAY, @dtnasc, @Hoje) / 365.25));
if (@Condicao<>(select idade from Cliente where id=@i))
update Cliente set Idade=@Condicao where id=@i;

set @i=@i+1;

end
go

Didn’t work.

What error or behaviour is shown?

– Luis Henrique

2015/03/05 at 17:23
Does not perform the update.

– Anderson

2015/03/05 at 17:43
You’re not telling me where to update. Ta missing clause where in the update, nay?

– mutlei

2015/03/05 at 18:17
@really mutlei this was missing. but I changed and continues the same way. Thanks !

– Anderson

2015/03/05 at 18:33
1

@Anderson Try to run the script without being on trigger and confirm that it is working. It may not be entering the if of update.

– Jothaz

2015/03/05 at 18:38
@Jota I did this... I really don’t think it’s entering if. Because SQLSERVER returns:"Command(s) completed successfully." But nothing happens....

– Anderson

2015/03/05 at 18:45

Show 1 more comment

1 answer

Browser other questions tagged sql sql-server datetime

You are not signed in. Login or sign up in order to post.

by Leonel Sanches da Silva • **88,623** points · Answer 1 · 2015-03-05T18:48:27+00:00

It won’t. Missed the use of the special table INSERTED, which precisely indicates the set of altered records of the table in question.

What you really want is a Stored Procedure. Trigger is only activated when some record is activated. In this case, it would be:

create procedure Atualiza_Idade
as 

    declare
    @i int,
    @dtnasc  datetime ,
    @Idade int,
    @Hoje DATETIME,
    @Condicao int

    SELECT @i = MIN(ID) FROM Cliente
    Set @Hoje=(SELECT GETDATE ( ))

    while (@i<(select COUNT( *) from Cliente ))
    begin
    select @dtnasc=DataNasimento,@Idade=Idade from Cliente where id=@i;

    set @Condicao = (SELECT FLOOR(DATEDIFF(DAY, @dtnasc, @Hoje) / 365.25));
    if (@Condicao<>(select idade from Cliente where id=@i))
    update Cliente set Idade=@Condicao where id=@i;

    set @i=@i+1;

end
go