August 13, 2007 at 2:59 pm
Hello all! I am a novice MS SQL Server user. Does anyone know how to create a "trigger" within a certain database table (in this case the table is called service), which will capture when a new record is created or when a record has been modified in a certain table. An example would be loading data into the service table. Before the data is loaded, there are 200 service records. After the data load there are 250 service records in the database. I would like the trigger feature to capture all the new records in the database (50). Any help would be greatly appreciated.
August 13, 2007 at 3:07 pm
Where do you want to send that new information?
You have to give more details or the answers we'll provide will be extremely generic... at best.
August 13, 2007 at 3:21 pm
Well in a simple world, I would like to write the results to a miscrosoft excel spreadsheet. If that's not possible, then I would like to have the ouput placed in another table, which I would have to create. Any suggestions on where the results should be stored?
August 13, 2007 at 3:34 pm
One possible way is to create a table with an identity column as the primary key, a "what" column that would hold the information so you know if the record was inserted, updated or deleted, a "who" column that would identify who did the action and a "when" column to hold when the action happened and then the data from the table you want to track, whether that is just the key column(s) or all of the column from the table you are auditing.
Where you what that table would be up to you - the same database or possibly one you use only for this type of audit data.
Then create the triggers to use the deleted and/or inserted tables to populate the data as you need using simple T-SQL.
August 14, 2007 at 11:51 am
Thank you fro your response. So are you saying I could have another database that will store the audit information?
Also, what is simple T-SQL? Do you mean SQL scripts?
August 14, 2007 at 1:17 pm
In one of my databases, I do something similar to what David mentioned: I have a facility table that has a date_added column, a date_updated column, and added_by_manager and updated_by_manager columns. The audit info is in the same database and actually, the very same table.
To capture the date_added, that field just has a default value of today's date (the getdate()) function. To capture the updated dates and the updating manager names, there is code in our Dreamweaver application that inserts and updates the dates and manager names.
To capture deletes, we use triggers to capture the information from the deleted table (see books online/help) for more about the deleted and updated tables that exist in SQL Server). A copy of the deleted record gets recorded in a new special table. Here's the basic SQL code to create the trigger:
CREATE
TRIGGER [trg_DeletedFacilities_I] ON [dbo].[facilities]
FOR
DELETE
AS
Insert
into facilities_deleted
(
facility_id
,
facility_name
,
facility_location
)
select
facility_id
,
facility_name
,
facility_location
,
from
deleted
There may be better, faster ways to do this stuff but the above has worked for us for a while.
August 14, 2007 at 1:27 pm
Personaly for the inserts and updates I like to add a handful of columns at the end of the row. For example CreateDate, CreatedBy, LastUpdateDate and LastUpdatedBy. I create them generically on all user tables then add triggers to update them. This of course assumes that you don't need to know every time in history that a row has changed.
Kenneth FisherI was once offered a wizards hat but it got in the way of my dunce cap.--------------------------------------------------------------------------------For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/[/url]Link to my Blog Post --> www.SQLStudies.com[/url]
August 14, 2007 at 1:44 pm
This also assumes that you don't have to comply with SOX.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply