Trigger to update existing records

Question

Trigger to update existing records

Minnu

SSCertifiable

Points: 6451
More actions
December 19, 2012 at 7:12 am

#267635

Hi Team,
Am having a trigger, which will update a column when insert / update occurs,
for new inserting or updating records trigger is working fine, but
am having a table, which already have multiple records, how to fire the same trigger for existing records to update column using a stored procedure

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

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

Minnu (12/19/2012)
Hi Team,
Am having a trigger, which will update a column when insert / update occurs,
for new inserting or updating records trigger is working fine, but
am having a table, which already have multiple records, how to fire the same trigger for existing records to update column using a stored procedure

i think you'll have to post the table definition(CREATE TABLE....) and the CREATE TRIGGER .... commands for us to be able to give you any sort of intelligent answer.

Post that, and be more specific about what you want to update, and i'm sure we can help.

it sounds more like you want to apply the same logic in the trigger against pre-existing data that existed before the trigger was added, and much less to do with the trigger itself, but i may be reading this wrong without more details.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Minnu SSCertifiable Points: 6451 More actions · Answer 2

Yes,

i want to update pre-existing data,

at present trigger is firing for only newly updated or inserted records.

i want Trigger should be fired one time for pre-existing data....

Please help

Lowell SSC Guru Points: 323504 More actions · Answer 3

Minnu (12/19/2012)
Yes,
i want to update pre-existing data,
at present trigger is firing for only newly updated or inserted records.
i want Trigger should be fired one time for pre-existing data....
Please help

well, here's the basic framework:

1. read the trigger code.

2. copy and paste it as an UPDATE statement

3., Modify/change the where statement to affect only records that are not yet affected.

again, without details, we can only offer vague suggestions.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Minnu SSCertifiable Points: 6451 More actions · Answer 4

Yes,

i want to update pre-existing data,

at present trigger is firing for only newly updated or inserted records.

i want Trigger should be fired one time for pre-existing data....

Please help

Ant-Green SSC Guru Points: 113445 More actions · Answer 5

Please post code, data and expected outcome as per the second link in my signature.

Minnu SSCertifiable Points: 6451 More actions · Answer 6

Am having a table called products with 4 columns, whenever any new insert or update happens,

column "Product_Name_Expiry" will be udpated with prouduct_Name : Product_expriry_date.

IDProduct_NameProduct_Expiry_Date Product_Name_Expiry

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

1001Veh2012-08-20 00:00:00.000 Veh: 2012-08-20 00:00:00.000

1075STL2012-08-20 00:00:00.000 STL: 2012-08-20 00:00:00.000

1081TKL2012-08-20 00:00:00.000 TKL: 2012-08-20 00:00:00.000

1569JKT2012-08-20 00:00:00.000 JKT: 2012-08-20 00:00:00.000

1012Veh2012-08-20 00:00:00.000 NULL

1013STL2012-08-20 00:00:00.000 NULL

1014TKL2012-08-20 00:00:00.000 NULL

1515JKT2012-08-20 00:00:00.000 NULL

ID : 1001, 1075, 1081,1569 are the new inserted / updated records, hence trigger fired and product_name_expriry,

My requirement i want to udpate proudct_name_expiry is without updating existing records "1012,1013,1014,1015"

i want a one time stored procedure to udpate existing records.

Lowell SSC Guru Points: 323504 More actions · Answer 7

Minnu (12/20/2012)
Am having a table called products with 4 columns, whenever any new insert or update happens,
column "Product_Name_Expiry" will be udpated with prouduct_Name : Product_expriry_date.
IDProduct_NameProduct_Expiry_Date Product_Name_Expiry
-------------------------------------- ---------------------------------
1001Veh2012-08-20 00:00:00.000 Veh: 2012-08-20 00:00:00.000
1075STL2012-08-20 00:00:00.000 STL: 2012-08-20 00:00:00.000
1081TKL2012-08-20 00:00:00.000 TKL: 2012-08-20 00:00:00.000
1569JKT2012-08-20 00:00:00.000 JKT: 2012-08-20 00:00:00.000
1012Veh2012-08-20 00:00:00.000 NULL
1013STL2012-08-20 00:00:00.000 NULL
1014TKL2012-08-20 00:00:00.000 NULL
1515JKT2012-08-20 00:00:00.000 NULL
ID : 1001, 1075, 1081,1569 are the new inserted / updated records, hence trigger fired and product_name_expriry,
My requirement i want to udpate proudct_name_expiry is without updating existing records "1012,1013,1014,1015"
i want a one time stored procedure to udpate existing records.

so, are you going to post the CREATE TRIGGER code and the CREATE TABLE definition?

If you want to perform the same update as the trigger, we need the code of the trigger.

you undoubtedly would have had a working answer yesterday if you had the chance to post the code.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PiMané SSChampion Points: 13965 More actions · Answer 8

If you don't have the UPDATED validation on the TRIGGER you can do a simple UPDATE

SET [col] = [col] and that will do nothing to your data but will fire the trigger.

Pedro

If you need to work better, try working less...

Minnu SSCertifiable Points: 6451 More actions · Answer 9

Updated by below query

UPDATE

SET [col] = [col]

but trigger is not firing on update.

below is my trigger

CREATE TRIGGER [dbo].[Prod]

ON [dbo].[Table_Name]

AFTER INSERT,UPDATE

Lowell SSC Guru Points: 323504 More actions · Answer 10

UPDATE

SET [col] = [col]

Seriously? that is the trigger body? updating the column to itself, with referencing the INSERTED virtual table?

Remember we are not in your cube looking over your shoulder; we can only help you if you help us by providing the complete, real update command.

there's got to be much much more than you are telling us, because base don what I've seen so far, it seems you are trying to do something like this:

UPDATE PROD

SET Product_Name_Expiry = getdate()

WHERE Product_Name_Expiry IS NULL

that is the most basic of UPDATE commands, and your trigger must be more complex than the code you pasted, otherwise you wouldn't need a trigger at all.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

PiMané SSChampion Points: 13965 More actions · Answer 11

Lowell (12/20/2012)
UPDATE
SET [col] = [col]
Seriously? that is the trigger body? updating the column to itself, with referencing the INSERTED virtual table?

It's not the trigger body... is the sql statement to fire the trigger on the table...

Try changing the AFTER INSERT, UPDATE to FOR INSERT, UPDATE.

The UPDATE table SET col = col should fire the trigger, I use it often when I want to do exactly what your're trying to accomplish..

If you need to work better, try working less...