Triggers and Inserts from Access Append Query?

[misscrf]

I originally posted this here thread702-1716327 , but it turns out my question is more about getting a sql trigger to work. I have an access form that triggers an append query, which inserts data into a sql table. The records are added to a table, and when they are, I need to add/update a calculated field. It needs to string together 2 different fields with a counter padded to 5 digits like this: "123456_00001_ABC" That counter is not based on the whole table. It is based on the count of items in that table that have the same 6 digit prefix (the first part of the concatenated string). I tried to set up a trigger, but when the insert happens, it updates for the whole table, not each record.

This is what I tried to do:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER MyAssetTrigger
AFTER UPDATE ON tblChainofCustody
FOR EACH ROW
WHEN AssetTag IS NULL --AND MATTER = A.MATTER
BEGIN
	UPDATE tblChainofCustody 
	SET AssetTag = a.NewAssetTag
	FROM (SELECT LEFT(vwb.BillingGroupKey,6) + '_' + RIGHT(REPLICATE('0', 5) + CAST(count(coc.ID) AS VARCHAR(5)), 5) + '_' + amt.MediaTypeAbbrev AS NewAssetTag
	FROM tblChainofCustody coc (NOLOCK) 
	JOIN tblAssetMediaTypes amt (NOLOCK) ON coc.FKMediaType = amt.ID 
	JOIN vw_billinggroups vwb (NOLOCK) ON vwb.BillingGroupKey like '%' + CONVERT(VARCHAR, coc.matter) + '%' 
	INNER JOIN INSERTED i on coc.ID = I.ID  WHERE I.ID = coc.ID
	GROUP BY amt.MediaTypeAbbrev, 
	LEFT(vwb.BillingGroupKey,6)) a
END

It won't let me alter it, though. I get this error:

Msg 102, Level 15, State 1, Procedure MyAssetTrigger, Line 2
Incorrect syntax near 'AFTER'.

This is the trigger that "works" but not as desired:

USE [230585NuixBilling]
GO
/****** Object:  Trigger [dbo].[MyAssetTrigger]    Script Date: 08/08/2013 17:21:37 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[MyAssetTrigger] 
ON [dbo].[tblChainofCustody]
FOR INSERT
AS
UPDATE tblChainofCustody set AssetTag = a.NewAssetTag
FROM
(SELECT LEFT(vwb.BillingGroupKey,6) + '_' + RIGHT(REPLICATE('0', 5) + CAST(count(coc.ID) AS VARCHAR(5)), 5) + '_' + amt.MediaTypeAbbrev AS NewAssetTag
FROM tblChainofCustody coc (NOLOCK) 
JOIN tblAssetMediaTypes amt (NOLOCK) ON coc.FKMediaType = amt.ID 
JOIN vw_billinggroups vwb (NOLOCK) ON vwb.BillingGroupKey like '%' + CONVERT(VARCHAR, coc.matter) + '%' 
INNER JOIN INSERTED i on coc.ID = I.ID  WHERE I.ID = coc.ID
GROUP BY amt.MediaTypeAbbrev, 
LEFT(vwb.BillingGroupKey,6)) a

The other part of this that I am missing is that I need to make sure that CAST(count(coc.ID) is only within the records with the same LEFT(vwb.BillingGroupKey,6).

Any thoughts?


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[djj55]

A trigger works on multiple rows at a time. Try (not tested):

ALTER TRIGGER MyAssetTrigger
AFTER UPDATE ON tblChainofCustody
BEGIN
	UPDATE tblChainofCustody 
	SET AssetTag = a.NewAssetTag
	FROM (SELECT LEFT(vwb.BillingGroupKey,6) + '_' + RIGHT(REPLICATE('0', 5) + CAST(count(coc.ID) AS VARCHAR(5)), 5) + '_' + amt.MediaTypeAbbrev AS NewAssetTag
	FROM tblChainofCustody coc (NOLOCK) 
	JOIN tblAssetMediaTypes amt (NOLOCK) ON coc.FKMediaType = amt.ID 
	JOIN vw_billinggroups vwb (NOLOCK) ON vwb.BillingGroupKey like '%' + CONVERT(VARCHAR, coc.matter) + '%' 
	INNER JOIN INSERTED i on coc.ID = I.ID  WHERE I.ID = coc.ID
	GROUP BY amt.MediaTypeAbbrev, 
	LEFT(vwb.BillingGroupKey,6)) a
WHERE AssetTag IS NULL --AND MATTER = A.MATTER
END

You may need to stop recursion as this trigger updates the table with the update trigger.

djj
The Lord is my shepherd (Psalm 23) - I need someone to lead me!

 

[misscrf]

Thank you. I will try it.

misscrf

It is never too late to become what you could have been ~ George Eliot