Trigger that calculates and updates age

Question

Trigger that calculates and updates age

Navigation

#1 by (4 votes)

3

I want to create a trigger that, when triggered, calculates the age of the client in each row of the table and updates if necessary.

I tried the following:

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

It did not work.

sql sql-server datetime

asked by anonymous 05.03.2015 / 18:08

1 answer

Search for nested list elements Segmentation Fault (core dumped): array reading

score 4 · Accepted Answer

You will not. The special table INSERTED , which indicates the changed recordset of the table in question, was missing.

What you really want is a Stored Procedure. The trigger is only activated when a record is activated. In this case, it would look like:

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