Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table where I want to do a AFTER UPDATE trigger if one of the columns modified was in a specific view

CREATE VIEW [dbo].[iv_EmployeesView]
AS
SELECT        EMPLOYEE_GUID AS EmployeeGuid, 
              VID AS Vid, 
              PREFIX AS Prefix, 
              FIRST_NAME AS FirstName, 
              LAST_NAME AS LastName, 
              SUFFIX AS Suffix
FROM            dbo.EMPLOYEES_M
GO

CREATE TRIGGER [dbo].[iv_EmployeesChanged] 
   ON  [dbo].[EMPLOYEES_M]
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    IF NOT(UPDATE(EMPLOYEE_GUID) OR UPDATE(VID) OR UPDATE(PREFIX) OR UPDATE(FIRST_NAME) OR UPDATE(LAST_NAME) OR UPDATE(SUFFIX))
        RETURN;

    insert into iv_PendingEmployeeChanges (RecordGuid, ChangeType)
         select EMPLOYEE_GUID, 'U' from INSERTED
END

Right now I add all of those IF NOT(UPDATE(EMPLOYEE_GUID) OR ... checks so I only trigger on the fields exposed by the view.

Is there a better way to do this to dynamically get all of the fields exposed by the view? I am concerned about future maintainability where someone will update the view but not update the triggers.

share|improve this question

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.