Doing a lookup in a trigger for a NOT NULL column? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Doing a lookup in a trigger for a NOT NULL column?

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-09-30 : 16:03:26

I have a table like this (yes, a self-joining hierarchy):

create table t (i int identity,[name] varchar(20),i_t_parent int,i_t_top int)

What I'd like is to allow direct access to the table for inserts (rather than using a SP) during the initial population. For simplicity, I'd like to just require people to enter the new name and parent (i_t_parent), and have the top level populated automatically (i_t_top).

The "top" is defined as the top level of the self-joining chain; the row where i_t_parent is NULL. For instance, if with the following data in the table:

i    name     i_t_parent   i_t_top
1    big      NULL         1
2    medium   NULL         2
3    small    NULL         3
4    red      1            1
5    green    1            1
6    striped  4            1

...I'd like to be able to do an "insert into t (name,i_t_parent) VALUES ('solid',5)" and have it automatically full in "1" for i_t_top.

Admittedly, I'll probably spend more time on this than it would take to write a simple web UI and SP's do to it, but it's become a challenge, and you know how that is.

It seems like it needs to be an instead of trigger, and I can guarantee that there will only be one insert at a time. But all of my attempts have failed. Can some kind soul point me in the right direction (or say "sorry, that's not possible")?

Thanks
-b

nr
SQLTeam MVY

12543 Posts

Posted - 2005-09-30 : 16:15:40

shouldn't need to be an instead of trigger.
Put a default on i_t_top to set to 1
The trigger then just needs to deal with the null parents

update t
set i_t_top = coalesce((select max(i_t_top) from t), 0) + 1
from inserted
where t.i = inserted.i
and inserted.i_t_parent is null

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

TimS
Posting Yak Master

198 Posts

Posted - 2005-09-30 : 16:40:50

update t1
set t1.i_t_top = COALESCE(t2.i_t_top, t1.i)
from inserted
join t t1 ON inserted.i= t1.i
LEFT JOIN t t2 ON t1.i_t_parent = t2.i

My Guess, untested, no warrenty above; for an after trigger

Edit: I would set the default to be an unused value like zero on i_t_top.

Tim S

aiken
Aged Yak Warrior

525 Posts

Posted - 2005-09-30 : 18:31:08

Damn, you guys are smart. Thanks!

-b

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources