No. Inside one trigger you can test whether a column was changed by using the UPDATE(fieldname) built-in function. This tests whether that field had a change as a result of the calling INSERT or UPDATE to the table that's involved.

Your triggers could look something like this for every action you want to log:

CREATE TRIGGER MyTRigger

ON UserCustomerRelation

AFTER UPDATE,INSERT

IF UPDATE(LastName)

INSERT INTO AuditLog(UserID, AuditInformation, AuditDate, AuditTypeID)

SELECT UserID ,'New LastName was added: ' + LastName,CURRENT_TIMESTAMP,[IDforCustomerAdd]

FROM INSERTED

GO

...where you would repeat that UPDATE() test for each important field that might have changed, all inside of the same single trigger.

That highly depends on what it is you'd like to get out of logging and how you plan to analyze/report on the audits. What does management actually want to know down the road? What kind of histories do they want to see?

The way you're looking to do it seems like an awful lot of records every time someone updates more than one aspect of their profile. If a woman gets married and changes last name and address, for example, your approach here requires more than one audit record just for that one profile update. Not very streamlined, but we don't know here what sort of history you'll want to see down the road.

Some shops are happy to prevent any UPDATEs to the table from the application, they'll only add a whole new record for each profile change, keep the ID# (which you could store uniquely in another table) and date stamp it to be the current record for that ID, while the old record is marked with the same date stamp as its expiration date. That way you could produce a whole history of full record changes for any ID you want without any audit procedures & triggers and the processing & space overhead that comes with them. But that's just one approach.

Hth,

-Ed