January 26, 2015 at 6:01 pm
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
January 26, 2015 at 7:45 pm
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 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
January 26, 2015 at 11:56 pm
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
Change is inevitable... Change for the better is not.
January 27, 2015 at 2:44 am
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 = ''.
January 27, 2015 at 9:57 am
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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
January 27, 2015 at 10:20 am
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.
January 29, 2015 at 5:53 am
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply