Time based trigger, left join conditions

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)

Time based trigger, left join conditions

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-07-20 : 09:58:58

Dan writes "Hey there.. nice site; bout time someone put together one especially for sql server :)

Anyway, I have two questions:
1) I have to put together an auction-based website, and as you know, auctions run for a limited amount of time. Because the web is a stateless environment and all the data (auction info) will be stored in a database, I figure it is probably safest and easiest to modify the data right from/at the source. So, how can I set a triger such that at exactly when the time listed in a column (auct_end) in a table (see below) sql server does an update?


auct_id | auct_start | auct_end | auct_over <-column name
int     | datetime   | datetime | bit       <-datatype

The only way that I can think of doing it is a fairly inefficient ad hoc solution: everytime a select or update of anysort are written to the database, it calls a trigger that will check the datediff of the auct_end column (all rows), and if it is past getdate() then it updates it (auct_over) to say that its finished. Is there a better way of doing this? Keep in mind that I can't do it from the ASP since (well for one reason you don't want to deal with it :) it is not a true application (in the sense that nothing is really 'running' unless someone is actively surfing the site) and I don't have admin access to the database (its stored on an ISP webserver).

Next question..
2) Until recently, I've only needed to do inner joins, and the way that I typically did them was using the old SQL-89 syntax (in the WHERE line). For a more complex query that I'm putting together, I need to use left outter joins, and according to the SQL Server 7 documentation the WHERE syntax (a.id *= b.id) may not be present in future versions, so I figured its in my best interests to just learn the SQL-92 style of putting it in the FOR tag. This is what I came up with:


select  inv.item_id,
	inv.desc,
	inv_pri.price
	from inventory as inv
	left outer join inventory_pricing as inv_pri on (inv.item_id=inv_pri.item_id)

But that is just part of it. You see, I only want to display the price if the user has previously requested a quote from the 'quotations' table, however I always want to display the item_id and the desc regardless. I know I could write this same query as this:


select  inv.item_id,
	inv.desc,
	(select inv_pri.price from inventory_pricing as inv_pri, 
        quotations as q where (inv_pri.item_id=inv.item_id) 
        and (q.item_id=inv.item_id) and (q.user_id=@user_id))
	from inventory as inv

but I don't think that its nearly as efficient (and it sure doesn't look very nice. My question is, can I do this (or something like this?):


select  inv.item_id,
	inv.desc,
	inv_pri.price
	from inventory as inv,
	quotations as q
	left outer join inventory_pricing as inv_pri 
        on ((inv.item_id=inv_pri.item_id) and (q.item_id=inv.item_id) 
        and (q.user_id=@user_id))

I've done quite a bunch of looking and can't seem to find these questions anywhere on your site, or the web for that matter. I hope they are sufficiently challenging :) Incase you want to know, I'm using SQL Server 7 and may be possibly upgrading to 2000 in the next year or so. If you have any questions, feel free to email me.

Thanks for the help! :)
-Dan"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources