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
add comment

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.