For the following table we've written an update trigger :
CREATE TABLE [dbo].[project] (
[project_id] [int] IDENTITY (1, 1) NOT NULL ,
[project_title] [udt_name2] NOT NULL ,
[project_description] [udt_desc1] NULL ,
[project_type_reference_id] [udt_ref_id] NULL ,
[requestor] [udt_n_user] NULL ,
[start_date] [udt_n_tsmp] NULL ,
[cost_center] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[status_reference_id] [udt_ref_id] NOT NULL ,
[group_id] [int] NOT NULL ,
[eta_date] [udt_n_tsmp] NULL ,
[percent_complete] [smallint] NULL ,
[status_note] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[rate_group_reference_id] [udt_ref_id] NULL ,
[spending_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[tape_submitted_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[on_air_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[deleted_flag] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[create_datetime] [udt_n_tsmp] NULL ,
[create_user] [udt_n_user] NULL ,
[last_upd_datetime] [udt_n_tsmp] NULL ,
[last_upd_user] [udt_n_user] NULL
) ON [PRIMARY]
GO
The update trigger is like ....
CREATE TRIGGER trg_project_update ON [dbo].[project]
FOR UPDATE
AS
DECLARE @project_id INT,
@column_name char(50),
@old_value char(100) ,
@new_value char(100) ,
@create_datetime smalldatetime,
@create_user varchar(20)
select @project_id = i.project_id from inserted i
/* Action Reference ID = 3 Would translate to "Project Updated" in
reference table*/
insert into dbo.audit(table_id, column_id, key1,
action_reference_id,create_datetime,create_user) values( 'project',
'table', @project_id, 3,@create_datetime,@create_user)
if update( project_description)
begin
select @new_value = i.project_description from inserted i
select @old_value = d.project_description from deleted d
select @column_name = 'project_description'
insert into dbo.audit(table_id, column_id, key1, action_reference_id,
field_value_before, field_value_after )
values( 'project', @column_name, @project_id, 1, @old_value,
@new_value)
end
if update( project_title)
begin
select @new_value = i.project_title from inserted i
select @old_value = d.project_title from deleted d
select @column_name = 'project_title'
insert into dbo.audit(table_id, column_id, key1, action_reference_id,
field_value_before, field_value_after )
values( 'project', @column_name, @project_id, 1, @old_value,
@new_value)
end
if update( project_type_reference_id)
begin
select @new_value = i.project_type_reference_id from inserted i
select @old_value = d.project_type_reference_id from deleted d
select @column_name = 'project_type_reference_id'
insert into dbo.audit(table_id, column_id, key1, action_reference_id,
field_value_before, field_value_after )
values( 'project', @column_name, @project_id, 1, @old_value,
@new_value)
end
Here as you can see ..
We'll be writting a repeating set of codes with only a small
difference among each other.
So, is there any generic way in T-Sql so that we can write a
fuction/Macro in T-sql so that we just need to pass the column-name
and the table as its parameters.And later we can call the
function/macro instead of repeating the code.
Thanks!