I am working on a Data Warehouse project and I am optimizing my OLTP database for data retrieval in order to source my stage OLAP database. As I am adding fields to my tables, instead of figuring out which applications are updating these tables or whether the tables are being updated by entity framework, I am managing the updates using Triggers, which are very powerful and very easy.

 

In this example, I have a Project table and have added a field entitled IsNewClient. When a project is inserted, I want to populate this field automatically based on the logic I have encapsulated in a user defined function. Also, if the Client field value is changed down the road for the Project, then I will need to reevaluate that value. So I also want to repopulate the IsNewClient field when only the Client field changes for the project.

 

INSERT TRIGGER

CREATE TRIGGER [dbo].[TriggerProjectInsert]

ON [dbo].[Project]

AFTER INSERT

AS

BEGIN

UPDATE [dbo].Project

SET IsNewClient = [dbo].[fnDetermineNewClientFromProject](ProjectId)

WHERE ProjectId IN (SELECT DISTINCT Inserted.[ProjectId] FROM Inserted)

END

 

UPDATE TRIGGER

CREATE TRIGGER [dbo].[TriggerProjectUpdate]

ON [dbo].[Project]

AFTER UPDATE

AS

BEGIN

    IF UPDATE([ClientId])

    BEGIN

        UPDATE [Project] SET [IsNewClient] = [dbo].[fnDetermineNewClientFromProject]([ProjectId])

        WHERE [Project].[ProjectId] IN (SELECT DISTINCT Inserted.[ProjectId] FROM Inserted)

    END

END