Always set a Field to NULL - is a trigger the answer

Question

Post reply

Always set a Field to NULL - is a trigger the answer

terry999

SSCertifiable

Points: 5033
More actions
January 26, 2015 at 6:01 pm

#294741

Hi
I would like to force a column to be null all the time. I cannot alter the table structure or alter the code that inserts data.
create table dbo.tblCustomer
(
CID int IDENTITY(1,1) not null,
Fnamevarchar(20) not null,
Lnamevarchar(20) not null,
Extravarchar(20) null
CONSTRAINT [PK_tblCustomer] PRIMARY KEY CLUSTERED
(CID ASC)
)
GO
create TRIGGER [dbo].[tr_Cust_insupd]
ON [dbo].tblCustomer
FOR insert, update
AS
-- Extra is always empty
if @@rowcount = 0
RETURN
SET NOCOUNT ON
UPDATE dbo.tblCustomer
SET Extra=NULL
FROM inserted tI
WHERE dbo.tblCustomer.CID = tI.CID
-- Worth checking for non empty?
AND dbo.tblCustomer.Extra <> ''
So when this is executed the field Extra is always NULL
INSERT INTO tblCustomer (Fname, Lname, Extra)
VALUES ('bob', 'smith', 'ignore'), ('jane', 'doe', 'empty')
update dbo.tblCustomer set Extra = 'something'
If I've understood After triggers correctly the data will be written and the trigger will fire and overwrite. To avoid 2 writes
I could create an INSTEAD OF trigger
CREATE TRIGGER TR_I_Customer
ON tblCustomer
INSTEAD OF INSERT AS
BEGIN
SET NOCOUNT ON
INSERT INTO tblCustomer
(Fname, Lname, Extra)
SELECT Fname, Lname, NULL
FROM Inserted
END
This will not write the "extra" field twice. However if a new Nullable column were added; it would be v.easy to forget to update the Instead Of trigger. Everything would still work OK but I would be effectively ignoring the new column as well.
What I would like in the instead of trigger is do something like this...
UPDATE INSERTED SET Extra=NULL
Continue with insert without supplying column /value list
What would be the best way of achieving this, trigger is the only way I could think of?
Thanks Terry

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Dwain Camps SSC Guru Points: 86908 More actions · Answer 1

The INSTEAD OF TRIGGER is probably what you want.

You can write a DATABASE TRIGGER to remind you when you ALTER the table and add a new column.

This article has an example of a database trigger that is much more complicated than you need, but you can probably figure out the simpler version. If not let me know and I'll try to help (although I haven't written many of those).

Archiving Hierarchical, Deleted Transactions Using XML[/url]

Another option would be a CHECK CONSTRAINT on the column to ensure any INSERTs always use a value of NULL, of course that means you might get some constraint failures you don't want.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jeff Moden SSC Guru Points: 1003861 More actions · Answer 2

What will it hurt if something IS entered into the column and can it wait? If so, save yourself some pain and just run a job on a scheduled basis to update the column where it isn't null.

Also, are there other triggers on this table? If so, what are they used for?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

terry999 SSCertifiable Points: 5033 More actions · Answer 3

Thanks for replying

dwain.c I hadn't thought of that

Jeff There are no pre-existing triggers.

I tried the periodical job but it takes ages to run. I'd expected first time to run to take ages as many rows had non blank column values. I suspect even the second run would take a long time: The columns are not part of an index and some of the columns are TEXT datatype

and default to '' which I can leave in.

so

UPDATE tbl###

SET Fld1= NULL, Fld2=NULL, Fld3=NULL

WHERE Fld1 IS NOT NULL OR Fld2 IS NOT NULL OR datalength(Fld3) <> 0

I use

datalength(Fld3) <> 0

because I don't mind if the TEXT field Fld3 = ''.

ScottPletcher SSC Guru Points: 100946 More actions · Answer 4

I'd stick with the AFTER trigger. Your initial trigger was pretty much spot on.

In case you create other AFTER triggers later, you might want to explicitly designate that trigger as the first (or last) one to fire after an INSERT/UPDATE:

EXEC sp_settriggerorder @triggername = 'dbo.tr_Cust_insupd',

@order = 'FIRST', @stmttype = 'INSERT'

EXEC sp_settriggerorder @triggername = 'dbo.tr_Cust_insupd',

@order = 'FIRST', @stmttype = 'UPDATE'

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

terry999 SSCertifiable Points: 5033 More actions · Answer 5

Thanks Scott

I had not thought about the order. Didn't know you could have more than one trigger for an action.

I'm going to stick with the AFTER trigger.

On real data I'd like to see the effect on the Transaction Log. I Think with this Trigger in place I'm doing 2 updates per single update/insert.

I wish the instead of trigger wouldn't make you have to code the insert/update.

David McKinney SSChampion Points: 10472 More actions · Answer 6

UPDATE dbo.tblCustomer

SET Extra=NULL

FROM inserted tI

WHERE dbo.tblCustomer.CID = tI.CID

-- Worth checking for non empty?

AND dbo.tblCustomer.Extra <> ''

If you want the column to be set always to null, then you shouldn't be checking for an empty string.

'' is not the same as null.

Null equivalent would be

AND dbo.tblCustomer.Extra is not null