At this point, we will create our trigger to automatically insert a record
into the History_Account_Info whenever an update or insert is made to the Account_Info
table. I will demonstrate how to create the triggers using SQL Server 7 Enterprise
Manager. Start by right-clicking the Account_Info table and selecting
"All Tasks" and then "Manager Triggers...".
At this point, the Trigger Properties dialog box then appears. This is where you enter your Trigger code. Let's first create an insert trigger, one that will fire when a record is inserted into the Account_Info table.
CREATE TRIGGER [insert_history] ON Account_Info
FOR INSERT
AS
INSERT History_Account_Info (history_account_id, history_account_name,
history_account_balance, modifier,
modified_date, action)
SELECT account_id, account_name, account_balance,
modifier, GETDATE(), 'INSERTED'
FROM inserted
Now click the Check Syntax button to verify that you have typed everything correctly, then click OK. Our last trigger that we will create will be an update trigger, one that fires when a record in the Account_Info table is updated.
CREATE TRIGGER [update_history] ON Account_Info
FOR UPDATE
AS
INSERT History_Account_Info (history_account_id, history_account_name,
history_account_balance, modifier,
modified_date, action)
SELECT account_id, account_name, account_balance,
modifier, GETDATE(), 'BEFORE UPDATE'
FROM deleted
INSERT History_Account_Info (history_account_id, history_account_name,
history_account_balance, modifier,
modified_date, action)
SELECT account_id, account_name, account_balance,
modifier, GETDATE(), 'AFTER UPDATE'
FROM inserted
Well, that is it! Now simply use your application and a history is recorded of all your changes. Or simply insert or update directly through SQL Server 7. Hope this helps some of you!
Comments