Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Trigger to update existing records Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 7:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:54 AM
Points: 227, Visits: 718
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

Post #1398388
Posted Wednesday, December 19, 2012 7:18 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 12,915, Visits: 32,074
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1398396
Posted Wednesday, December 19, 2012 7:26 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:54 AM
Points: 227, Visits: 718
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
Post #1398405
Posted Wednesday, December 19, 2012 7:38 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 12,915, Visits: 32,074
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1398414
Posted Wednesday, December 19, 2012 8:11 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:54 AM
Points: 227, Visits: 718
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
Post #1398434
Posted Wednesday, December 19, 2012 8:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 2:20 AM
Points: 5,231, Visits: 5,106
Please post code, data and expected outcome as per the second link in my signature.



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1398436
Posted Thursday, December 20, 2012 2:16 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:54 AM
Points: 227, Visits: 718
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.

ID Product_Name Product_Expiry_Date Product_Name_Expiry
-- ------------ ------------------------ ---------------------------------
1001 Veh 2012-08-20 00:00:00.000 Veh : 2012-08-20 00:00:00.000
1075 STL 2012-08-20 00:00:00.000 STL : 2012-08-20 00:00:00.000
1081 TKL 2012-08-20 00:00:00.000 TKL : 2012-08-20 00:00:00.000
1569 JKT 2012-08-20 00:00:00.000 JKT : 2012-08-20 00:00:00.000

1012 Veh 2012-08-20 00:00:00.000 NULL
1013 STL 2012-08-20 00:00:00.000 NULL
1014 TKL 2012-08-20 00:00:00.000 NULL
1515 JKT 2012-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.
Post #1398868
Posted Thursday, December 20, 2012 5:51 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 9:23 AM
Points: 12,915, Visits: 32,074
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.

ID Product_Name Product_Expiry_Date Product_Name_Expiry
-- ------------ ------------------------ ---------------------------------
1001 Veh 2012-08-20 00:00:00.000 Veh : 2012-08-20 00:00:00.000
1075 STL 2012-08-20 00:00:00.000 STL : 2012-08-20 00:00:00.000
1081 TKL 2012-08-20 00:00:00.000 TKL : 2012-08-20 00:00:00.000
1569 JKT 2012-08-20 00:00:00.000 JKT : 2012-08-20 00:00:00.000

1012 Veh 2012-08-20 00:00:00.000 NULL
1013 STL 2012-08-20 00:00:00.000 NULL
1014 TKL 2012-08-20 00:00:00.000 NULL
1515 JKT 2012-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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #1398930
Posted Thursday, December 20, 2012 5:59 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, September 8, 2014 3:14 AM
Points: 513, Visits: 1,130
If you don't have the UPDATED validation on the TRIGGER you can do a simple UPDATE [table] 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...
Post #1398934
Posted Thursday, December 20, 2012 6:10 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 8:54 AM
Points: 227, Visits: 718

Updated by below query

UPDATE [table] 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
Post #1398936
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse