November 20, 2008 at 7:43 pm
Sorry for my ignorance, but I'm not normally a programmer. I'm developing the first of many triggers to audit 3 fields in a production table to a custom audit table (other triggers will also populate this table for many other tables).
My question is this if most of the fields in the audit table to be populated with NULL values do I define 1 scalar NULL variable @NULLVariable as type ? and then set @NULLVariable = NULL , or do I need to create separate variables for int, bit, char, nchar, varchar and datetime fields ?
I believe not all NULLs where created equal so I think I need to declare and set multiple data type variables to NULL to achieve this functionality.
Thanks in Advance
Adonia
November 20, 2008 at 7:47 pm
Without seeing the code for the trigger, hard to say, but, why not just code the insert statement in the trigger with NULL where you need to put nulls instead of using variables.
If you show the code, you may get a better answer.
November 20, 2008 at 7:54 pm
Okay here's the code
create TRIGGER [dbo].[TRG_AUDIT_UPDATE_SITES]
ON [dbo].[REGISTERED_SITES]
AFTER INSERT, UPDATE
AS
BEGIN
declare @NULLvarchar100varchar(100),
@NULLvarchar60varchar(60),
@NULLintint,
@NULLbitbit,
@NULLchar1char(1),
@NULLchar2char(2),
@NULldatetimedatetime,
@NULLnchar10nchar(10)
set@NULLvarchar100 = NULL;
set@NULLvarchar60 = NULL;
set@NULLint = NULL;
set@NULLbit = NULL;
set@NULLchar1 = NULL;
set@NULLchar2 = NULL;
set@NULldatetime = NULL;
set@NULLnchar10 = NULL;
SET NOCOUNT ON;
If update(SITE_NAME)
begin
Insert into dbo.SITE_PUBLICATION_CHANGE_HISTORY
(SITE_NAME,
SITE_ID,
RESOLUTION_ID,
PUBLISHED_BY,
PUBLISHED_DATE,
S5A_APPLIES,
S5B_APPLIES,
S5C_APPLIES,
S5D_APPLIES,
S392A_APPLIES,
S392B_APPLIES,
S392C_APPLIES,
S392D_APPLIES,
RESTRICTION_CODE,
ACCESS_CODE,
COORDINATES,
SPATIAL_ACCURACY_CODE,
STATUS_CODE,
ENTRY_DATE,
SITE_TYPE_CODE,
ADDITIONAL_INFO)
Select inserted.SITE_NAME,
inserted.SITE_ID,
@NULLint,
inserted.LAST_UPDATED_BY,
inserted.LAST_UPDATED,
@NULLbit,
@NULLbit,
@NULLbit,
@NULLbit,
@NULLbit,
@NULLbit,
@NULLbit,
@NULLbit,
@NULLchar2,
@NULLchar1,
@NULLnchar10,
@NULLchar1,
@NULLchar2,
@NULLdatetime,
@NULLnchar10,
@NULLnchar10
From inserted
Full outer join deleted
On inserted.Site_ID = deleted.Site_ID
Where inserted.SITE_NAME != deleted.SITE_NAME
Or inserted.SITE_NAME is not null and deleted.SITE_NAME is null
Or inserted.SITE_NAME is null and deleted.SITE_NAME is not null
--- repeat for other columns to be audited
end
end
November 20, 2008 at 8:03 pm
all you really need is this:
Insert into dbo.SITE_PUBLICATION_CHANGE_HISTORY
(SITE_NAME,
SITE_ID,
RESOLUTION_ID,
PUBLISHED_BY,
PUBLISHED_DATE,
S5A_APPLIES,
S5B_APPLIES,
S5C_APPLIES,
S5D_APPLIES,
S392A_APPLIES,
S392B_APPLIES,
S392C_APPLIES,
S392D_APPLIES,
RESTRICTION_CODE,
ACCESS_CODE,
COORDINATES,
SPATIAL_ACCURACY_CODE,
STATUS_CODE,
ENTRY_DATE,
SITE_TYPE_CODE,
ADDITIONAL_INFO)
Select inserted.SITE_NAME,
inserted.SITE_ID,
null, -- @NULLint,
inserted.LAST_UPDATED_BY,
inserted.LAST_UPDATED,
null, -- @NULLbit,
null, -- @NULLbit,
null, -- @NULLbit,
null, -- @NULLbit,
null, -- @NULLbit,
null, -- @NULLbit,
null, -- @NULLbit,
null, -- @NULLbit,
null, -- @NULLchar2,
null, -- @NULLchar1,
null, -- @NULLnchar10,
null, -- @NULLchar1,
null, -- @NULLchar2,
null, -- @NULLdatetime,
null, -- @NULLnchar10,
null -- @NULLnchar10
From inserted
Full outer join deleted
On inserted.Site_ID = deleted.Site_ID
Where inserted.SITE_NAME != deleted.SITE_NAME
Or inserted.SITE_NAME is not null and deleted.SITE_NAME is null
Or inserted.SITE_NAME is null and deleted.SITE_NAME is not null
You don't need to declare all those "null" variable types.
November 20, 2008 at 9:26 pm
Thanks Lynn
I had considered it was possible, but now I know I can do this . Progamming has never been a strength or passion of mine so I usually only do it when neccessary, which is obviously not enough.
Regards Adonia
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply