Gustav Brock
Gustav at cactus.dk
Tue Jul 15 08:17:50 CDT 2008
Hi Shamil
Didn't mean to push you ..
Clean and simple solution, by the way. Thanks!
/gustav
>>> shamil at smsconsulting.spb.ru 15-07-2008 14:40 >>>
Hi All,
Below is a solution simple trigger, which I promised to publish here last
Friday, and which I somehow missed to write last week trapped by many other
details/features to work on. That happens.
There is nothing that interesting in this trigger as far as I see now after
weekend - just wanted to close this thread by posting what I announced to be
posted.
Thank you.
--
Shamil
P.S.
--
-- Sets [Order].OrderRecalcNeeded flag to 1 when
-- any of [Order] table's fields are updated
--
-- Note: OrderRecalcNeeded flag field's value
-- can be also updated (e.g. set to 0)
-- together with other fields but such update
-- will be overwritten by this trigger.
-- Although if only OrderRecalcNeeded is
-- updated then this trigger is fired but
-- it doesn't set OrderRecalcNeeded to 1
--
Create trigger [dbo].[OrderUpdateTrigger]
on [dbo].[Orders]
for update
as
set nocount on
begin
if
(
UPDATE([CustomerId]) OR
UPDATE([EmployeeId]) OR
UPDATE([OrderDate]) OR
UPDATE([RequiredDate]) OR
UPDATE([ShippedDate]) OR
UPDATE([ShipVia]) OR
UPDATE([Freight]) OR
UPDATE([ShipName]) OR
UPDATE([ShipAddress]) OR
UPDATE([ShipCity]) OR
UPDATE([ShipRegion]) OR
UPDATE([ShipPostalCode]) OR
UPDATE([ShipCountry])
)
update [Orders]
set
OrderRecalcNeeded = 1
where OrderId in
(
select i.OrderId
from inserted i, deleted d
where i.OrderId = d.OrderId
)
end
GO
-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, July 11, 2008 3:12 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Feeling myself really stupid today - OT(?): MS
SQL2000/2005 development issue...
Hi Shamil
Isn't that what a T-SQL trigger is for? Or, for this "lengthy
[AnalyseOrderDetailsAndCalcTotals] procedure", a CLR SQL Server trigger?
http://msdn.microsoft.com/en-us/library/938d9dz2.aspx
Pass the flag to the trigger. If the flag is zero, the trigger just exits.
Or - as I would suspect you to - if you have a DataTable object, catch the
CRUD events like:
private void ChildInsertTrigger()
{
_childDataTable.TableNewRow += new
DataTableNewRowEventHandler(_childDataTable_TableNewRow);
}
void _childDataTable_TableNewRow(object sender,
DataTableNewRowEventArgs e)
{
// Pop a message.
MessageBox.Show(e.Row.ToString());
// Run trigger code if flag of _parentDataTableRow is True.
If (_parentRow.ExecuteFlag == True)
{
// Run lengthy [AnalyseOrderDetailsAndCalcTotals] procedure
}
}
I much prefer the last option as I have the opinion that higher level logic
and procedures should be kept off the database engine.
Looking forward to the clever solution from you and your friend!
/gustav
>>> shamil at smsconsulting.spb.ru 11-07-2008 00:21 >>>
Hi All,
I must say I'm feeling myself really stupid today as I have got trapped for
a couple of hours by a simple(?) task to solve - here it is (I'm sorry if
the task definition below sounds ambiguous):
Environment
===========
MS SQL 2000/2005
Task
====
A table should be able to mark itself as requiring batch time consuming
calculation to be executed by setting *its own* CalcRequired flag field when
any of its fields are getting updated, or when any or its child table's rows
are getting inserted, updated or deleted.
The same CalcRequired flag should be still possible to set or to *clear* by
explicit UPDATE sql statement.
(Note: The definition of the above task is almost complete but some
ambiguity in its definition is left to force you to think through and to
effectively solve all the possible cases)
Restrictions:
=============
- there couldn't be any other auxiliary/temporary tables used in this task
solutuion
Typical application:
====================
1. An [Order] and an [OrderDetail] tables.
2. [Order] table has to get lengthy [AnalyseOrderDetailsAndCalcTotals]
procedure to be scheduled for execution when any of the [Order] table row
fields are updated or when any of the child [OrderDetail] table rows related
to the parent [Order] table row are inserted, updated or deleted
3. The scheduling of [AnalyseOrderDetailsAndCalcTotals] procedure to run is
considered to be done when [Order].[CalcRequired] flag field is set to 1.
4. Lengthy [AnalyseOrderDetailsAndCalcTotals] procedure can be activated
e.g. by Windows Service, which analyses value of [Order].[CalcRequired]
flag, or this could be an MS SQL server job, which runs periodically, starts
stored procedure, which checks value of [Order].[CalcRequired] flag of
[Order] table rows, and runs [AnalyseOrderDetailsAndCalcTotals] (stored)
procedure for the ones of them having this flag field equal to 1; when
calculation is done then flag is set back to 0 (cleared). Or calculation can
be done by an external program, (web) service etc., which supply the values
to be used to update [Order] and [OrderDetail] table's rows and then clear
[Order].[CalcRequired] flag field.
Solution
=========
- I think I have one to be published and criticized and improved in a day
here, do you?
Note
=====
the above task seems to be an ordinary routine one but I somehow didn't need
to get it done in the past, and therefore I got "trapped" by it today having
many other complicated things todo and thinking this simple one can be
solved by "cowboy attack": - I was wrong! - I needed to talk to my colleague
to get it solved, and after he proposed the solution/approach I did think
before but somehow didn't elaborate/dig into it good enough to get it done
by myself...
If you do have the solution for the above task from you past experience
please wait and do not publish it here within a day, please give the others
a chance to get it done by themselves...
It's really simple when you have its solution at your hands...
Or I'm missing something again today?
Thank you.
--
Shamil