Trigger to populate a column

Question

Trigger to populate a column

Minnesota - Viking

Hall of Fame

Points: 3469
More actions
July 8, 2010 at 3:45 pm

#94983

I have a table Person
Name(varchar(10)) Age(int) Gender(M/F) Code(Autogenerated,PK)
Jim 20 M 7654
I need a new column in here with name old_age
Which saves 20 in it when ever Age is modified.
Example if the above record is updated as
Jim 30 M 7654
I need a new colum which saved what existed in Age..
And it need not be dynamic .. liek if 30 is changed again i dont care.. as long as i have 20 saved
Name(varchar(10)) Age(int) Gender(M/F) Code(Autogenerated,PK) New_age
Jim 30 M 7654 20
So for this do i write a Insert after Trigger ? or ????
Please help.
[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 1

What I really don't understand is why you would store a persons age. After all it changes as time goes by. Why not store the persons birth date and calculate the age. Anyway, here is the trigger you can use. I really don't like giving it to you as I think it is a bad solution to store the age instead of birth date. But hey, maybe you have a reason that I just don't understand.

create trigger <trigger_name> on Person

for update

as

if update(age)

update p set old_age=age from inserted i inner join Person p on i.code=p.code

Minnesota - Viking Hall of Fame Points: 3469 More actions · Answer 2

I appreciate your curiosity.

Its just a sample table i created in my mind to post it here.

In fact i have a status in place of Age.

And I care about its original form since its going to be updated multiple times anyways.

Well i will try ur code and update u.. wait.. lemmie do that now so that i need not post two comments.

Ok ii tried the code.. Every new record being inserted is populated by '0' in old_age field instead of the previous age from Age field ?

By the by what is Inserted table ??? and what is CODE ?

[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 3

inserted is a table created for a trigger with new values when you insert or update a row.

If you want the old value, you would create a trigger that actually joins deleted, which is the table containing the old values. The code given above needs a trigger and table name, but it will update a field in the row that was updated with the new value, not the old.

To learn this, you should SELECT from inserted and deleted in the trigger, comparing what was in the table before with the changes to understand. I would urge you to experiment, and if you don't understand something, post some code along with the specific thing you don't understand or think is happening. We are happy to help, but we aren't here just to write all your code for you.

Minnesota - Viking Hall of Fame Points: 3469 More actions · Answer 4

@steve-2

1)

Initially i tried an AFTER INSERT trigger but it gave an error of "Table is mutating"

Later i realized that it was dumb mistake since i was asking my trigger to update a value from a record which is on its way to be COMMITTED.

So i felt that i dnt need to post that code.

And by da by when i asked the previous commenter Nils about "What is CODE"

It is not source code. I forgot that the field name of my sample table was CODE

I know its again stupid to forget our own creations, bt i was not forcing for CODE.

2)

by the by thnx for the info on inserted and deleted tables. I never knew they existed.

I wonder what went wrong in my learning curve.

But i cudnt select from inserted or deleted. I tried SELECT * FROM inserted/deleted

I changed DB to model n master to see if this table resides in them ?? But No.. It always said

Invalid Object Name.

3)

As you said the above code wud return NEW value.. it is true..

I logged in to update that it is returning the NEW value ..bt u already said that.

thnx

But wen i deleted that trigger and re created new one with deleted in place of Inserted ..its still returned the same NEW value.

-- Creating new trigger using Inserted. (Nils Code)

create trigger trigger123 on Person

for update

as

if update(age)

update P set old_age=P.age from inserted i inner join Person p on i.code=p.code

-- Creating new trigger using deleted.

create trigger trigger1234 on Person

for update

as

if update(age)

update P set old_age=p.age from deleted d inner join Person p on d.code=p.code

SELECT * FROM Person

-- Insert New Record

INSERT INTO Person VALUES ('Sam1','34','F','')

-- Initial Verification

SELECT * FROM Person WHERE FirstName = 'Sam1'

FirstName Age Gender Code old_age

---------- ----------- ------ ----------- -----------

Sam1 34 F 20010 0

(1 row(s) affected)

-- Update sam1's age to 55 to see if the trigger kicks in

UPDATE Person SET Age = 55 WHERE Code = 20010

-- Final Verification

FirstName Age Gender Code old_age

---------- ----------- ------ ----------- -----------

Sam1 55 F 20010 55 .........> --supposed to be 34

(1 row(s) affected)

SELECT * FROM inserted

SELECT * FROM deleted

--Msg 208, Level 16, State 1, Line 1

--Invalid object name 'inserted'.

[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]

Sergiy SSC Guru Points: 110208 More actions · Answer 5

Remove trigger "using inserted". You don't need it.

Use "using deleted" only.

Put

SELECT * FROM inserted

SELECT * FROM deleted

into the trigger code.

Those table exist only while trigger is running and are accessible only from trigger code.

Remove those selects when you've done with testing and ready to put the trigger into Production code.

_____________
Code for TallyGenerator

Minnesota - Viking Hall of Fame Points: 3469 More actions · Answer 6

@@ Sergiy

Thank You Sir , I got to know how to look at inserted n deleted.

Just FYI the "using inserted" and "using deleted" is da part of comment

Not actual code, sorry for poor highlighting ..

[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]

Nils Gustav Stråbø SSChampion Points: 11259 More actions · Answer 7

Steve Jones - Editor (7/8/2010)
If you want the old value, you would create a trigger that actually joins deleted, which is the table containing the old values.

Silly me!! How could I do such a basic error. 🙁

Minnesota - Viking Hall of Fame Points: 3469 More actions · Answer 8

@ Nils and Steves

Yes, it now gives me the new field with old value.

BUT i want that to happen only once, like i dont want that trigger to act on every update,

Is there a way i can limit that triggers action to only first update on that record?

again reiterating:

I dont want the newly created filed to be updated every time age is changed, just once, for teh first time shd be enough.

[font="Verdana"]
Today is the tomorrow you worried about yesterday:-)[/font]

souLTower SSCarpal Tunnel Points: 4026 More actions · Answer 9

Set the default value for the new field to null.

Modify your delete trigger to only update if the new field value is null:

update myTable set myField = oldValue where id = deleted.id and oldValue is null

Steve Jones - SSC Editor SSC Guru Points: 736504 More actions · Answer 10

I like the last suggestion. Set the column to null, only update if it's null. That will also tell you if the value has ever been updated.