Semi computed column only possible with after insert trigger?

Question

I have a column on every table in my database that needs to be computed for new rows, but has legacy values that need to be preserved. I have a working solution that uses an after update trigger, but something about it doesn't sit right with me. Especially the idea of having such a trigger for every table. I'm hoping there are other ideas that I've simply overlooked.

I'd be really happy if there's a way to do it with a DEFAULT column value, but that doesn't seem possible. As far as I know, there's no way for a DEFAULT scalar function to see the other values being inserted into the row.

Here's a sample database to demonstrate my current solution. I put it on SQL Fiddle for convenience:

http://sqlfiddle.com/#!3/65f88/1

But here's the code to recreate it (in case the SQL Fiddle becomes inaccessible for any reason in the future):

CREATE TABLE tmpTest (
    id INT IDENTITY PRIMARY KEY,
    origin INT DEFAULT 15,
    code VARCHAR(9),
    name VARCHAR(100),
    UNIQUE (code)
)

GO

CREATE TRIGGER tmpTest_code ON tmpTest
AFTER INSERT AS
    UPDATE t SET
      t.code = RIGHT(UPPER(master.dbo.fn_varbintohexstr(t.origin)), 2)
             + RIGHT(UPPER(master.dbo.fn_varbintohexstr(t.id)), 7)
    FROM tmpTest t 
    INNER JOIN inserted i ON i.id = t.id
    WHERE t.code IS NULL

GO

INSERT INTO tmpTest (name) VALUES ('one')
INSERT INTO tmpTest (name) VALUES ('two')
INSERT INTO tmpTest (name) VALUES ('three')
INSERT INTO tmpTest (name) VALUES ('four')
INSERT INTO tmpTest (name) VALUES ('five')
INSERT INTO tmpTest (name) VALUES ('six')
INSERT INTO tmpTest (name) VALUES ('seven')
INSERT INTO tmpTest (name) VALUES ('eight')
INSERT INTO tmpTest (name) VALUES ('nine')
INSERT INTO tmpTest (name) VALUES ('ten')

INSERT INTO tmpTest (origin, code, name) VALUES (3, '030000001', 'legacy one')
INSERT INTO tmpTest (origin, code, name) VALUES (3, '030000002', 'legacy two')
INSERT INTO tmpTest (origin, code, name) VALUES (3, '030000003', 'legacy three')
INSERT INTO tmpTest (origin, code, name) VALUES (3, '030000004', 'legacy four')
INSERT INTO tmpTest (origin, code, name) VALUES (3, '030000005', 'legacy five')
INSERT INTO tmpTest (origin, code, name) VALUES (3, '030000006', 'legacy six')
INSERT INTO tmpTest (origin, code, name) VALUES (3, '030000007', 'legacy seven')
INSERT INTO tmpTest (origin, code, name) VALUES (3, '030000008', 'legacy eight')
INSERT INTO tmpTest (origin, code, name) VALUES (3, '030000009', 'legacy nine')
INSERT INTO tmpTest (origin, code, name) VALUES (3, '03000000A', 'legacy ten')

bear in mind that master.dbo.fn_varbintohexstr is an udocumented function... — Mitch Wheat, Oct 10 '12 at 23:07
please see this answer, which lets you use a simple computed column: http://stackoverflow.com/questions/6637096/varbinary-to-varchar-w-o-master-dbo-fn-varbintohexstr — Mitch Wheat, Oct 10 '12 at 23:08
Oh, thanks for that. I was going to worry about that later, but now I don't have to. Woohoo. — Carey Tilden, Oct 10 '12 at 23:17
I don't see a way around the trigger, but if the trigger code is generic enough, you can auto-create it for new tables with a DDL-Trigger. Basically you define a trigger that fires on a table creation and then creates the after insert trigger. — TToni, Oct 12 '12 at 08:27
Thanks for the suggestion, @TToni. That should at least ease one of my primary maintenance concerns. — Carey Tilden, Oct 12 '12 at 17:37

score 0 · Answer 1 · answered Nov 20 '12 at 09:20

If the requirement for the code column is that it is UNIQUE, why not alter it to have a default value of, say, NEWID() while leaving all current values in place?

Another way to do what you need is to create another column that would take a value of

ISNULL(code, <formula based on id and origin)

This way, for existing records, its value will match code, and for new values, where code is presumably null, it will match your new formula.

Either way, it should avoid trigger solution which is unpleasant, I agree.

Last but not least, I would use HASHBYTES instead of fn_varbintohexstr

Semi computed column only possible with after insert trigger?

1 Answers1