October 27, 2009 at 1:05 pm
Using SQL Server 2005 in 64 bit environment.
Have 2 tables Contacts (table 1) and ContactActivity (table 2).
Trying to create a trigger so that when the Contact.ContactSource and Contact.ContactSoureSpecific fields (Table 1) are inserted or updated that the trigger will see if there is an existing record in the ContactActivity table (Table 2) based on the ContactID, CompanyID, QVISellingCompany and SourceSpecific fields. If these fields do not match, then create a new record in the ContactActivity table with the original/existing Contacts (Table 1) data before new data is inserted/updated into the Contacts table (Table 1)
If record exists in the ContactActivity table (Table 2) then update existing record with the changed data from the Contacts table (Table 1).
See table specifications and trigger code below along with errors that I get when attempting to compile/create the trigger.
What am I doing incorrectly?
Table 1:
TableName ColumnName TypeName max_length PRECISION scale
Contact ContactID uniqueidentifier 16 0 0
ContactsCompanyID uniqueidentifier 16 0 0
ContactsEmployeeID uniqueidentifier 16 0 0
ContactsCampaignID uniqueidentifier 16 0 0
ContactsNamePrefix nvarchar 12 0 0
ContactsFirstName nvarchar 40 0 0
ContactsMiddleInit nvarchar 2 0 0
ContactsLastName nvarchar 60 0 0
ContactsNameSuffix nvarchar 12 0 0
ContactsContactSource nvarchar 60 0 0
ContactsContactSourceSpecific nvarchar 60 0 0
ContactsSalesStatus smallint 2 5 0
ContactsStreet1 nvarchar 100 0 0
ContactsStreet2 nvarchar 100 0 0
ContactsStreet3 nvarchar 100 0 0
ContactsQVIStreet4 nvarchar 100 0 0
ContactsCity nvarchar 70 0 0
ContactsState nvarchar 10 0 0
ContactsZip nvarchar 30 0 0
ContactsCountry nvarchar 4 0 0
ContactsFax nvarchar 50 0 0
ContactsPhone nvarchar 50 0 0
ContactsPhoneDescr smallint 2 5 0
ContactsPhoneExt nvarchar 12 0 0
ContactsEMailAddress nvarchar 510 0 0
ContactsWebPageURL nvarchar 200 0 0
ContactsSalutation nvarchar 30 0 0
ContactsTitle nvarchar 100 0 0
ContactsJobCategory nvarchar 60 0 0
ContactsComments nvarchar 510 0 0
ContactsCreationDate datetime 8 23 3
ContactsChangeDate datetime 8 23 3
ContactsUser1 (QVISellingCompany) nvarchar 150 0 0
ContactsUser2 nvarchar 150 0 0
ContactsUser3 nvarchar 150 0 0
ContactsChangeDateOffline datetime 8 23 3
ContactsChangedByID uniqueidentifier 16 0 0
ContactsChangedByIDOffline uniqueidentifier 16 0 0
ContactsTerritoryID uniqueidentifier 16 0 0
Table 2:
TableName ColumnName TypeName max_length PRECISION scale
ContactActivity ContactID uniqueidentifier 16 0 0
ContactActivity CompanyID uniqueidentifier 16 0 0
ContactActivity QVISellingCompany nvarchar 20 0 0
ContactActivity Source nvarchar 60 0 0
ContactActivity SourceSpecific nvarchar 60 0 0
ContactActivity SourceUser2 nvarchar 60 0 0
ContactActivity EventDate datetime 8 23 3
ContactActivity Comments nvarchar 510 0 0
ContactActivity CreationDate datetime 8 23 3
ContactActivity ChangeDate datetime 8 23 3
ContactActivity CompanyName nvarchar 100 0 0
ContactActivity FirstName nvarchar 40 0 0
ContactActivity MiddleInit nvarchar 4 0 0
ContactActivity LastName nvarchar 60 0 0
ContactActivity ComboSource nvarchar 100 0 0
ALTER TRIGGER [ContactsIUp]
ON [dbo].[Contacts]
FOR INSERT, UPDATE
AS
--- NEW CODE 2009-10-20 rss - to create new records in ContactActivity table containing previous/original data prior to
--- updates to the Contact Source/Contact Source Specific fields in the Contacts table.
SELECT
ii.ContactID
, ii.CompanyID
, ii.User1
, ii.ContactSource
, ii.ContactSourceSpecific
, dd.ContactID
, dd.CompanyID
, dd.User1
, dd.ContactSource
, dd.ContactSourceSpecific
, dd.User2
, dd.User3
, dd.Comments
, dd.FirstName
, dd.MiddleInit
, dd.LastName
, ca.ContactID
, ca.CompanyID
, ca.QVISellingCompany
, ca.Source
, ca.SourceSpecific
, ca.SourceUser2
, ca.EventDate
, ca.Comments
, ca.CompanyName
, ca.FirstName
, ca.MiddleInit
, ca.LastName
from inserted as ii, deleted as dd, ContactActivity as ca
--- compares new Contact data to existing ContactActivity data
IF
ISNULL (ii.ContactID, CHAR(1)) <> ISNULL (ca.ContactID, CHAR(1))
AND ISNULL (ii.CompanyID, CHAR(1)) <> ISNULL (ca.CompanyID, CHAR(1))
AND ISNULL (ii.User1, CHAR(1)) <> ISNULL (ca.QVISellingCompany, CHAR(1))
AND ISNULL (ii.ContactSource, CHAR(1)) <> ISNULL (ca.Source, CHAR(1))
AND ISNULL (ii.ContactSourceSpecific, CHAR(1)) <> ISNULL (ca.SourceSpecific, CHAR(1))
--- copies existing Contact records into QVIContactActivity to keep record of all prior activity
UPDATE dbo.ContactActivity
SET ContactID = dd.ContactID
, CompanyID = dd.CompanyID
, QVISellingCompany = dd.QVISellingCompany
, Source = dd.Source
, SourceSpecific = dd.SourceSpecific
, SourceUser2 = dd.User2
, EventDate = dd.User3
, Comments = dd.Comments
, CompanyName = (Select XCOMP_tblCompany.CompanyName from XCOMP_tblCompany where XCOMP_tblCompany.CompanyID = dd.ContactID)
, FirstName = dd.FirstName
, MiddleInit = dd.MiddleInit
, LastName = dd.LastName
WHERE
dd.ContactID = ca.ContactID
AND dd.CompanyID = ca.CompanyID
AND dd.QVISellingCompany = ca.QVISellingCompany
AND dd.Source = ca.Source
AND dd.SourceSpecfic = ca.SourceSpecific
AND dd.User3 = ca.EventDate
ERRORS:
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 46
The multi-part identifier "ii.ContactID" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 46
The multi-part identifier "ca.ContactID" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 46
The multi-part identifier "ii.CompanyID" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 46
The multi-part identifier "ca.CompanyID" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 46
The multi-part identifier "ii.User1" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 46
The multi-part identifier "ca.QVISellingCompany" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 46
The multi-part identifier "ii.ContactSource" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 46
The multi-part identifier "ca.Source" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 46
The multi-part identifier "ii.ContactSourceSpecific" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 46
The multi-part identifier "ca.SourceSpecific" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 54
The multi-part identifier "dd.ContactID" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 54
The multi-part identifier "ca.ContactID" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 54
The multi-part identifier "dd.CompanyID" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 54
The multi-part identifier "ca.CompanyID" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 54
The multi-part identifier "dd.QVISellingCompany" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 54
The multi-part identifier "ca.QVISellingCompany" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 54
The multi-part identifier "dd.Source" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 54
The multi-part identifier "ca.Source" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 54
The multi-part identifier "dd.SourceSpecfic" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 54
The multi-part identifier "ca.SourceSpecific" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 54
The multi-part identifier "dd.User3" could not be bound.
Msg 4104, Level 16, State 1, Procedure ContactsIup, Line 54
The multi-part identifier "ca.EventDate" could not be bound.
October 27, 2009 at 1:28 pm
Please read the first article I reference below in my signature block regarding asking for assistance. Follow the instructions in that article regaring posting the DDL (CREATE TABLE statements), sample data for both initial state of the tables as well as updates/inserts in the tables, and expected results. If you do that, you will get much better help as well as tested code in return.
You may also be shown other ways to accomplish what you are trying to accomplish.
October 27, 2009 at 2:06 pm
Well, the first thing I saw was the multi cross join you have between the deleted, inserted, and your table, in your select, in the trigger. Next is you can't select information in a trigger.
So you might want to fix this, and explain what you are trying to accomplish, maybe there is an easier way. I mean, there probably is,:-P.
Post some code, and what you are trying to do, we'll try to help.
Cheers,
J-F
October 27, 2009 at 2:12 pm
Well, looking more closely at your code, I can say the error you are getting comes from your IF Statement, and because you are referencing fields that are not in a select. You need to either add the from clause with the tables you are willing to query, or change the way you are working.
Also, you need to think set based, this trigger would only work for 1 row at a time, and a trigger needs to be built for multi rows. That means, you cannot use a value in a if statement, to see if it changed, you need to think set based for this example. It will be quicker, so it's a plus.
Post some code, and we'll help,
Cheers,
J-F
October 27, 2009 at 2:32 pm
J-F Bergeron (10/27/2009)
Well, the first thing I saw was the multi cross join you have between the deleted, inserted, and your table, in your select, in the trigger. Next is you can't select information in a trigger.So you might want to fix this, and explain what you are trying to accomplish, maybe there is an easier way. I mean, there probably is,:-P.
Post some code, and what you are trying to do, we'll try to help.
I saw that also. But after a cursory look, I just thought it best to point the OP to the article and ask for code, ddl, sample data, and expected results.
Didn't catch what you saw later.
October 27, 2009 at 2:47 pm
Attempting to follow the Form Etiquette guide as posted. Using the QuoteName example however I have Nulls in many fields and the text output only shows one column "NULL" with no other values. I am using SQL 2005. The QuoteName fails when it hits a null value.
What is the workaround for this?
Is it recommended to extract the entire table (i.e all 60 fields/columns) or just a subset of the data (fields and/or rows)?
Thanks.
October 27, 2009 at 2:55 pm
Screen print of quotename code and the incorrect results (in text output).
October 28, 2009 at 7:24 am
Sorry I couldn't post earlier,
You can add the ISNULL builtin function to pass a empty string to the quotename function when the value is null.
Here's how:
QuoteName(ISNULL(CompanyID,''),'''')
That behavior happens when you append a null value to a string, it nullifies it.
Cheers,
J-F
October 28, 2009 at 9:01 am
Further Clarification and Data File attachment.
Using SQL Server 2005 in 64 bit environment.
Have 2 tables CON_tblContact (table 1) and QVIContactActivity (table 2).
Trying to create a trigger so that when the CON_tblContact.ContactSource and CON_tblContact.ContactSourceSpecific fields are inserted or updated in the CON_tblContact table then the trigger would see if there is an existing record in the QVIContactActivity table. The criteria that a record would match would be that the ContactID, CompanyID, QVISellingCompany, SourceSpecific and EventDate fields were the same /exact match. The CON_tblContact.ContactSource must = “Trade Show” for records to be evaluated.
The trigger would check the deleted data (original value) and see if there is a matching record in the QVIContact activity table. If no matching record then the trigger would create a new record in the QVIContactActivity table based on the deleted data.
The trigger would then check the inserted data (new value) and see if there is a matching record in the QVIContact activity table. If no matching record then the trigger would create a new record in the QVIContactActivity table based on the inserted data.
If records match, then the trigger would do nothing
Basically we want to track all changes of the ContactSource/ContactSourceSpecific fields (if the ContactSource = “Trade Show”) from the CON_tblContacts table and have those tracking records in the QVIContactActivity table.
Example 1:
CON_tblContact:
Firstname Lastname Source SourceSpecific EventDate QVISellingCompany ContactID CompanyID
John Smith Trade Show Quality Expo 2009 10/24/2009 OGP 1234 AD54321
QVIContactActivity:
No matching record – trigger creates new record based on the CON_tblContact data above.
Example 2:
CON_tblContact: (original existing data)
Firstname Lastname Source SourceSpecific EventDate QVISellingCompany ContactID CompanyID
John Smith Trade Show Quality Expo 2009 10/24/2009 OGP 1234 AD54321
CON_tblContact: (user updates record with new/corrected data)
Firstname Lastname Source SourceSpecific EventDate QVISellingCompany ContactID CompanyID
John Smith Trade Show EastTech Show 08/12/2009 OGP 1234 AD54321
QVIContactActivity: (existing records before CON_tblContact change above)
Firstname Lastname Source SourceSpecific EventDate QVISellingCompany ContactID CompanyID
John Smith Trade Show Quality Expo 2009 10/24/2009 OGP 1234 AD54321
QVIContactActivity: (trigger creates a new record from the CON_tblContact inserted data)
Firstname Lastname Source SourceSpecific EventDate QVISellingCompany ContactID CompanyID
John Smith Trade Show Quality Expo 2009 10/24/2009 OGP 1234 AD54321
John Smith Trade Show EastTech Show 08/12/2009 OGP 1234 AD54321
Please find attached a text file with the data, table specs, inserts and problem trigger code.
Please let me know if there is anything missing as this is my first attempt at posting this detail.
Thanks.
October 28, 2009 at 9:04 am
The formatting of my example in the previous post did not work correctly. Attached is Word document with the correct formatting for better readability.
October 28, 2009 at 9:06 am
Can a trigger call or run a stored procedure?
Can a trigger call or run a stored procedure and then return back to the same trigger for futher processing?
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply