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
