May 28, 2008 at 8:24 am
Thank you, I'm testing it now.
May 28, 2008 at 8:30 am
toniupstny (5/28/2008)
What Jack said. Looks like I am much slower than him.Toni
I wouldn't say "much". This happens to me all the time.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 28, 2008 at 9:11 am
Everything works great the only issue I seem to have is if I do a manual change to the table, I get an error.
(The Row value(s) updated or deleted either do not make the row unique or they alter multiple rows(2 rows))
I don't understand this, any explanation would be helpful. thanks
May 28, 2008 at 9:26 am
Post the schema of all your tables involved and the trigger you have in place and exactly what you are modifying. Sounds like you have a unique constraint/index that is being modified and failing.
When you say manually making a change do you mean typing in the table results in SSMS? If so, do you get the same error if you script the same change, "update table set column = value where column2 = value2"?
You may want to use profiler to trace what is happening in the manual modification also.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 28, 2008 at 9:46 am
Whats a good way or format to give you the schema in? Also not sure what you mean by SSMS?
May 28, 2008 at 9:48 am
Just googled SSMS, and yes i am using that.
May 28, 2008 at 10:25 am
mbender (5/28/2008)
Whats a good way or format to give you the schema in? Also not sure what you mean by SSMS?
Sorry, SSMS is SQL Server Management Studio which replaced Enterprise Manager with SQL Server 2005. I forgot we were in a 7, 2000 forum.
Something like what I posted in my last set of code. In SSMS/EM I would right click the table and gp to Script Table as -> Create To -> clipboard and paste the code into a code block here for each table. That should script out columns, indexes, foreign keys, etc... Then I would do the same thing for my trigger. Then do give some sample data like you did earlier. This way we can duplicate your environment.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 28, 2008 at 11:03 am
This is my Table
SELECT [TcaPKey]
,[AgrPKey]
,[AloPKey]
,[TcaCustomerName]
,[TcaContactName]
,[TcaContactPhone]
,[TcaContactExtension]
,[TcaLocationContact]
,[TcaLocationContactPhone]
,[TcaLocationContactExtension]
,[TcaLocationName]
,[TcaLocationAddress1]
,[TcaLocationAddress2]
,[TcaLocationCity]
,[TcaLocationState]
,[TcaLocationZipCode]
,[TcaBillingMailingName]
,[TcaBillingAddress1]
,[TcaBillingAddress2]
,[TcaBillingCity]
,[TcaBillingState]
,[TcaBillingZipCode]
,[TcaType]
,[TcaStatus]
,[TcaPurchaseOrder]
,[TcaEquipment]
,[TcaEquipmentPartNumber]
,[TcaSerialNumber]
,[TcaResponseTime]
,[TcaHours]
,[TcaProblem]
,[TcaSolution]
,[TcaNotes]
,[TcaEngineer]
,[TcaOffice]
,[TcaCallDate]
,[TcaCallAge]
,[TcaCallTime]
,[TcaPageTime]
,[TcaAknowledgeTime]
,[TcaStartDate]
,[TcaStartTime]
,[TcaCompletedDate]
,[TcaCompletedTime]
,[TcaClosedDate]
,[TcaOneTrip]
,[TcaMetResponse]
,[TcaDriveTime]
,[TcaLaborTime]
,[TcaPartsCost]
,[TcaLaborCost]
,[TcaTax]
,[TcaTotalCost]
,[TcaSystemPrinter]
,[TcaSiteAuditLevel]
,[TcaPoCheck]
,[TcaClosedBy]
,[TcaEnteredBy]
,[TcaRepeatCall]
,[TcaFreeLabor]
,[TcaVerificationStatus]
,[TcaUseCreditCard]
,[TcaCreatedBy]
,[TcaCreatedDate]
,[TcaPageAge]
,[TcaDispatchTurnaround]
,[TcaModifiedDate]
FROM [T2OnlineBackup].[dbo].[TicketCallMain]
This is my Trigger
ALTER TRIGGER [dbo].[trAgrPkeyHistory]
ON [dbo].[TicketCallMain]
FOR UPDATE
AS
IF UPDATE(AgrPKey) -- remove this if you want to log changes other than to AgreeID
BEGIN
INSERT INTO TicketCallMainModifiedHistory
SELECT
D.TcaPkey,
D.AgrPKey,
D.AloPKey,
D.TcaType,
D.TcaStatus,
D.TcaEngineer,
D.TcaOffice,
GetDate() as TcaModifiedDate
FROM
inserted I JOIN -- new record
deleted D ON -- existing record
I.TcaPkey = D.TcaPkey AND
I.AgrPKey <> D.AgrPKey -- this ensures that AgreeID was changed
END
This is some sample data
INSERT INTO #mytable
(Ticket, AgreeId, LocId, Customer, Type)
SELECT '177987','2206','1103','Bobs BBQ','M' UNION ALL
SELECT '177988','3123','1345','Bobs Box of Toys','K' UNION ALL
SELECT '177980','2111','1654','Bobs Burgers','B' UNION ALL
SELECT '177990','1206','1289','Bobs Bakery','M' UNION ALL
SELECT '177991','3206','3241','Bobs Bar','M' UNION ALL
SELECT '177992','2223','3621','Bobs Shanty','M' UNION ALL
I just tried changing it SQL Enterprise Manager and it took without a problem, in SQL Server Management Studio Express it gave me an error. All i did is opened the table and changed the AgrPkey number for a record to something else.
May 28, 2008 at 1:55 pm
If that is your Identity column, you would have to turn that off before making a change?
Toni
Viewing 9 posts - 16 through 23 (of 23 total)
You must be logged in to reply to this topic. Login to reply