May 28, 2012 at 8:58 am
I need to amend a trigger on an existing SQL Server /MS Access application that I have inherited.
The trigger is for INSERT, UPDATE and is on my contact table, itblContacts. It writes values to the parent planning_applications table for legacy reasons
My addition is to write a value to the 'Agent' field on the Planning_Applications table such that it will either be the ContactName or the Organisation Name of the contact
This is my addition to the trigger in the main body inserted after the original code...
DECLARE @AgentValue nvarchar(50)
SET @AgentValue = (CASE WHEN @MapToOrganisationValue IS NULL THEN @MapToContactNameValue ELSE @MapToOrganisationValue END)
UPDATE [planning_applications]
SET [agent] = @AgentValue
FROM dbo.Planning_Applications P INNER JOIN
itblContacts C ON P.AltRef = C.AltRef AND P.AppCode = C.AppCode
WHERE (C.ContactType = 2) AND P.AltRef = @AltRefValue AND p.AppCode = @AppCodeValue
Notes: The @MapToOrganisationValue, @MapToContactNameValue, @AltRefValue and @AppCodeValue are all set in the original trigger and are fine. @AltRefValue and @AppCodeValue form the Primary key for both tables. C.ContactType=2 means the contact is an 'agent'. In summary; update the planning applications table with an agent value if an agent type contact for that planning_application is found. If no agents, then no update should occur. This database is distributed to many customers and we cannot update values using SSIS and I do not want to do this in the front-end (unless I have to)
This part of the trigger doesn't use the inserted pseudo-table as the intention is to ensure that the agent value in Planning_Applications gets updated regardless of which contacttype has just been updated or inserted. If there is more than 1 'agent' type contact, then this doesn't matter as long as Agent gets updates to at least one of them.
This all works fine when the contact record is updated by hand from SQL Management Studio and when updated in the Access linked table, however when updated from the Access form, NULL gets inserted as Agent in Planning_Applications. The form is blind to the trigger.
I appreciate that this is probably not the best way to manage this, but in most cases there is only a handful of contacts per planning_application and in reality it will be rare if more than 1 agent is specified, but possible.
This is the first trigger I have ever worked with, so I am hoping that there is something simple I have overlooked (I've recently been thrown in at the deep end at a new job!)
Apologies if this is the wrong forum. All critique and advice accepted 😀
May 28, 2012 at 9:38 am
Could you please post the complete trigger code.
May 28, 2012 at 10:28 am
Hi. Entire trigger is as follows...
USE [FastPLANNINGDev40]
GO
/****** Object: Trigger [dbo].[ispitblContactsIUTrig] Script Date: 05/28/2012 10:37:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[ispitblContactsIUTrig] ON [dbo].[itblContacts]
FOR INSERT, UPDATE
AS
DECLARE @UpdateRequired bit
SET @UpdateRequired = 0
IF (SELECT COUNT(*) FROM zstblAddSettings WHERE strVariable = 'Contacts: Synchronise Legacy Contact Data' AND strValue = 'Yes') > 0
BEGIN
SET CONCAT_NULL_YIELDS_NULL OFF
--Destination Column Names
DECLARE @AltRef varchar(30), @AppCode nvarchar(4), @DestTableName nvarchar(128)
DECLARE @MapToOrganisation nvarchar(128), @MapToAddressPrefix nvarchar(128), @MapToNumberAndRoad nvarchar(128)
DECLARE @MapToSecondRoadnvarchar(128), @MapToLocalitynvarchar(128), @MapToTownnvarchar(128), @MapToCountynvarchar(128)
DECLARE @MapToPostcodenvarchar(128), @MapToContactName varchar(128), @MapToEmailAddress nvarchar(128), @MapToFaxNumber nvarchar(128)
DECLARE @MapToMobile nvarchar(128), @MapToTelephone nvarchar(128), @MapToLanguage nvarchar(128)
DECLARE @MapToContactRef nvarchar(128), @MapToLetterRef nvarchar(128)
--Destination field values
DECLARE @AltRefValue nvarchar(30), @AppCodeValue nvarchar(4)
DECLARE @MapToOrganisationValue nvarchar(70), @MapToAddressPrefixValue nvarchar(220), @MapToNumberAndRoadValue nvarchar(220)
DECLARE @MapToSecondRoadValuenvarchar(50), @MapToLocalityValuenvarchar(50), @MapToTownValuenvarchar(50), @MapToCountyValuenvarchar(40)
DECLARE @MapToPostcodeValuenvarchar(18), @MapToContactNameValue nvarchar(75), @MapToEmailAddressValue nvarchar(255), @MapToFaxNumberValue nvarchar(15)
DECLARE @MapToMobileValue nvarchar(35), @MapToTelephoneValue nvarchar(35), @MapToLanguageValue nvarchar(25)
DECLARE @MapToContactRefValue nvarchar(128), @MapToLetterRefValue nvarchar(128)
DECLARE @sql nvarchar(1500), @SQL2 nvarchar(1500), @SQL3 nvarchar(4000)
--Fetch Column Names
SELECT
@AltRef =zstblAddToSQL.UniqueIdentifier, @AppCode = inserted.AppCode, @DestTableName = zstblAddToSQL.KeyTableName,
@MapToOrganisation = M.MapToOrganisation, @MapToAddressPrefix = M.MapToAddressPrefix, @MapToNumberAndRoad = M.MapToNumberAndRoad,
@MapToSecondRoad = M.MapToSecondRoad, @MapToLocality = M.MapToLocality, @MapToTown = M.MapToTown, @MapToCounty = M.MapToCounty,
@MapToPostcode = M.MapToPostcode, @MapToContactName = M.MapToContactName, @MapToEmailAddress = M.MapToEmailAddress, @MapToFaxNumber = M.MapToFaxNumber,
@MapToMobile = M.MapToMobile, @MapToTelephone = M.MapToTelephone, @MapToLanguage = M.MapToLanguage, @MapToContactRef = M.MapToContactRef, @MapToLetterRef = M.MapToLetterRef
FROMisysContactTypes CROSS JOIN isysContactMapping M INNER JOIN
zstblAddToSQL ON M.LegacyContactTable = zstblAddToSQL.KeyTableName
JOIN inserted ON inserted.ContactType = isysContactTypes.ContactTypeID
WHEREM.IsApplicationTable = 1 AND zstblAddToSQL.ApplicationCode = inserted.AppCode
AND (M.ContactTypeCode = isysContactTypes.ContactTypeID OR M.ContactTypeCode = isysContactTypes.SubType)
SELECT @AltRefValue = inserted.AltRef,
@AppCodeValue= inserted.AppCode,
@MapToOrganisationValue= inserted.Organisation,
@MapToAddressPrefixValue= inserted.AddressPrefix,
@MapToNumberAndRoadValue= inserted.NumberAndRoad,
@MapToSecondRoadValue= inserted.SecondRoad,
@MapToLocalityValue= inserted.Locality,
@MapToTownValue= inserted.Town,
@MapToCountyValue= inserted.County,
@MapToPostcodeValue= inserted.PostCode,
@MapToContactNameValue= inserted.ContactName,
@MapToEmailAddressValue= inserted.EmailAddress,
@MapToFaxNumberValue= inserted.FaxNumber,
@MapToMobileValue= inserted.Mobile,
@MapToTelephoneValue= inserted.Telephone,
@MapToLanguageValue= inserted.PreferredLanguage,
@MapToContactRefValue= inserted.ContactCode,
@MapToLetterRefValue = inserted.LetterRef
FROM inserted
SET @sql = N'UPDATE [' + @DestTableName + N'] SET '
IF (@MapToOrganisation IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToOrganisation + N'] = @MapToOrganisationValue1, '
SET @UpdateRequired = 1
END
IF (@MapToAddressPrefix IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToAddressPrefix + N'] = @MapToAddressPrefixValue1, '
SET @UpdateRequired = 1
END
IF (@MapToNumberAndRoad IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToNumberAndRoad + N'] = @MapToNumberAndRoadValue1, '
SET @UpdateRequired = 1
END
IF (@MapToSecondRoad IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToSecondRoad + N'] = @MapToSecondRoadValue1, '
SET @UpdateRequired = 1
END
IF (@MapToLocality IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToLocality + N'] = @MapToLocalityValue1, '
SET @UpdateRequired = 1
END
IF (@MapToTown IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToTown + N'] = @MapToTownValue1, '
SET @UpdateRequired = 1
END
IF (@MapToCounty IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToCounty + N'] = @MapToCountyValue1, '
SET @UpdateRequired = 1
END
IF (@MapToPostcode IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToPostcode + N'] = @MapToPostcodeValue1, '
SET @UpdateRequired = 1
END
IF (@MapToContactName IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToContactName + N'] = @MapToContactNameValue1, '
SET @UpdateRequired = 1
END
IF (@MapToEmailAddress IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToEmailAddress + N'] = @MapToEmailAddressValue1, '
SET @UpdateRequired = 1
END
IF (@MapToFaxNumber IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToFaxNumber + N'] = @MapToFaxNumberValue1, '
SET @UpdateRequired = 1
END
IF (@MapToMobile IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToMobile + N'] = @MapToMobileValue1, '
SET @UpdateRequired = 1
END
IF (@MapToTelephone IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToTelephone + N'] = @MapToTelephoneValue1, '
SET @UpdateRequired = 1
END
IF (@MapToLanguage IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToLanguage + N'] = @MapToLanguageValue1, '
SET @UpdateRequired = 1
END
IF (@MapToContactRef IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToContactRef + N'] = @MapToContactRefValue1, '
SET @UpdateRequired = 1
END
IF (@MapToLetterRef IS NOT NULL)
BEGIN
SET @sql = @sql + N'[' + @MapToLetterRef + N'] = @MapToLetterRefValue1, '
SET @UpdateRequired = 1
END
--remove the trailing comma
SET @sql = LEFT(@SQL, LEN(@SQL) - 1)
SET @sql = @sql + N' WHERE [' + @AltRef + N'] = @AltRefValue1 AND [AppCode] = @AppCodeValue1'
SET @SQL2 = N'@AltRefValue1 nvarchar(30), @AppCodeValue1 nvarchar(4), @MapToOrganisationValue1 nvarchar(70), @MapToAddressPrefixValue1 nvarchar(220), '
SET @SQL2 = @SQL2 + N'@MapToNumberAndRoadValue1 nvarchar(220), @MapToSecondRoadValue1 nvarchar(50), @MapToLocalityValue1 nvarchar(50), @MapToTownValue1 nvarchar(50), '
SET @SQL2 = @SQL2 + N'@MapToCountyValue1 nvarchar(40), @MapToPostcodeValue1 nvarchar(18), @MapToContactNameValue1 nvarchar(75), @MapToEmailAddressValue1 nvarchar(255), '
SET @SQL2 = @SQL2 + N'@MapToFaxNumberValue1 nvarchar(15), @MapToMobileValue1 nvarchar(35), @MapToTelephoneValue1 nvarchar(35), @MapToLanguageValue1 nvarchar(25), @MapToContactRefValue1 nvarchar(15), @MapToLetterRefValue1 nvarchar(100) '
SET @SQL3 = @SQL2 + @sql
--INSERT INTO a (b) VALUES (@SQL3)
--EXECUTE sp_ExecuteSQL @SQL3, @SQL2, @AltRefValue1=@AltRefValue, @AppCodeValue1=@AppCodeValue, @MapToOrganisationValue1=@MapToOrganisationValue, @MapToAddressPrefixValue1=@MapToAddressPrefixValue, @MapToNumberAndRoadValue1=@MapToNumberAndRoadValue, @MapToSecondRoadValue1=@MapToSecondRoadValue, @MapToLocalityValue1=@MapToLocalityValue, @MapToTownValue1=@MapToTownValue, @MapToCountyValue1=@MapToCountyValue, @MapToPostcodeValue1=@MapToPostcodeValue, @MapToContactNameValue1=@MapToContactNameValue, @MapToEmailAddressValue1=@MapToEmailAddressValue, @MapToFaxNumberValue1=@MapToFaxNumberValue, @MapToMobileValue1=@MapToMobileValue, @MapToTelephoneValue1=@MapToTelephoneValue, @MapToLanguageValue1=@MapToLanguageValue
IF (@UpdateRequired = 1)
BEGIN
EXEC sp_ExecuteSQL @sql, @SQL2, @AltRefValue, @AppCodeValue, @MapToOrganisationValue, @MapToAddressPrefixValue, @MapToNumberAndRoadValue, @MapToSecondRoadValue, @MapToLocalityValue, @MapToTownValue, @MapToCountyValue, @MapToPostcodeValue, @MapToContactNameValue, @MapToEmailAddressValue, @MapToFaxNumberValue, @MapToMobileValue, @MapToTelephoneValue, @MapToLanguageValue, @MapToContactRefValue, @MapToLetterRefValue
--ADDITIONAL SQL STARTS HERE
DECLARE @AgentValue nvarchar(50)
set @AgentValue = (CASE WHEN @MapToOrganisationValue IS NULL THEN @MapToContactNameValue ELSE @MapToOrganisationValue END)
UPDATE [planning_applications]
SET [agent] = @AgentValue
FROM dbo.Planning_Applications P INNER JOIN
itblContacts C ON P.AltRef = C.AltRef AND P.AppCode = C.AppCode
WHERE (C.ContactType = 2) AND P.AltRef = @AltRefValue AND p.AppCode = @AppCodeValue
--ADDITIONAL SQL ENDS HERE
END
END
I've marked where my addition in included. The rest is all legacy and working as expected
May 28, 2012 at 11:15 am
Offhand it appears that your Access form is not filling in either one of the ContactName or the Organisation columns.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 28, 2012 at 1:02 pm
Hi. The data is definately being entered via the form. In fact it was a long time before I realised the trigger was working via other means because I was focussed on the form data-entry. It is highly consistent. Direct via table - OK, entered via Form (based on the same table - no query) - Agent field gets updated as NULL. I can't even get a literal value into the Agent field (e.g. SET Agent='HELLO') to trigger from the form
May 28, 2012 at 10:56 pm
Then you need to use profiler to see what's really going in SQL.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 29, 2012 at 2:45 am
I decided to see if some of the seemingly over-complicated form events in place were preventing the trigger in some way and before I got very far, I found that this is now all working as expected today. This is after 4 days of not working at all.
It is great that it is working, but of course irritating as I do not know why it wasn't working initially. Nothing has changed in the front end, except that it has been compacted, which may have been all it needed.
I couldn't find any reference to this issue anywhere on the internet, so if someone else encounters it, hopefully this thread will help them
Thanks for your ideas.
May 29, 2012 at 3:11 am
Your trigger is written in such way that it can only work for update/insert of a single record at the time. It's a classical example of how "never write your trigger like that"
The trigger is raised once for modification operation, regardless of how many records will be affected.
May 29, 2012 at 3:29 am
Hi Eugene
Only one record at a time does get updated via user data entry. Are you referring to the original trigger I inherited or to the modification I made to it?
As I said, this is the first trigger I have ever worked with
May 29, 2012 at 4:02 am
I'm not going to fix the whole your trigger, but the following should give the idea of how it should be done.
The existing part for updating Planning_Applications :
DECLARE @AgentValue nvarchar(50)
set @AgentValue = (CASE WHEN @MapToOrganisationValue IS NULL THEN @MapToContactNameValue ELSE @MapToOrganisationValue END)
UPDATE [planning_applications]
SET [agent] = @AgentValue
FROM dbo.Planning_Applications P INNER JOIN
itblContacts C ON P.AltRef = C.AltRef AND P.AppCode = C.AppCode
WHERE (C.ContactType = 2) AND P.AltRef = @AltRefValue AND p.AppCode = @AppCodeValue
Should be replaced with:
UPDATE P
SET [agent] = ISNULL(C.Organisation, C.ContactName)
FROM dbo.Planning_Applications P
JOIN inserted C
ON C.AltRef = P.AltRef
AND C.AppCode = P.AppCode
WHERE C.ContactType = 2
AND P.[agent] != ISNULL(C.Organisation, C.ContactName)
You can see the difference? It will work regardless of how many many records are inserted/updated in
[itblContacts]. You don't even need to join to the table itself, INSERTED is much smaller and it will have all required new values to use. Please also note of check "P.[agent] != ISNULL(C.Organisation, C.ContactName)". You don need to check "IF (@UpdateRequired = 1)"
What about if update did happen but the values are not changed? What about if number of records updated is greater than 1 (let say you've been asked to do data-fix)? Your check will evaluate one record only. The check in WHERE will work for all.
May 29, 2012 at 4:23 am
Many thanks for the information. I will take the advice on board 🙂
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply