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

CREATETRIGGER[dbo].[TriggerProjectInsert]

ON[dbo].[Project]

AFTERINSERT

AS

BEGIN

UPDATE[dbo].Project

SETIsNewClient=[dbo].[fnDetermineNewClientFromProject](ProjectId)

WHEREProjectIdIN(SELECTDISTINCTInserted.[ProjectId]FROMInserted)

END

 

UPDATE TRIGGER

CREATETRIGGER[dbo].[TriggerProjectUpdate]

ON[dbo].[Project]

AFTERUPDATE

AS

BEGIN

IFUPDATE([ClientId])

BEGIN

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

WHERE[Project].[ProjectId] IN(SELECTDISTINCTInserted.[ProjectId]FROMInserted)

END

END

Advertisements