Got a new biz rule that fits a trigger implementation.

Table = Orders.
It already has 2 triggers in place on insert so adding a third.

NewRule is to update columns from a metadata rules for this particular
client.  The rules will come via a cursor, 1-5 rules (chage data input to
"official client manner" ) or rows will need to be processed.  This cursor
will contain id cols to find the rule, another for the COLUMN of data to
change and the VALUE we want applied.
col,                      value
SHIPMETHOD, "USPS  "

I was thinking of creating a string var for all of the columns / values and
using that @set in my update statement.


UPDATE Orders SET @set WHERE Orderid=@OrderID


Is this a good plan of action or is there another way outside of a cursor?

TIA

Stephen,

Are these values to be used stored in a table somewhere?  Can you just use
this table (or create it if it doesn't exist) to specify the values to be
used i.e., an UPDATE based on another table.  If so see the 'UPDATE'
statement examples in the SQL BOL.

HTH

Jerry


Show quoteHide quote

Yes the coulmn name and value are stored in another table, and I may have 5
col's to update for a specific type of order.

This is more of a data entry correction requested by a few of our business
partners.  We are an outsource for them and when an order is placed into our
system from them, they want us to "correct" a few columns.

In a % of total this will be applied to 15% of our daily orders, but as word
spreads that we can do this it may hit 50% of every order getting rest.

These orders are from our interface, as well as batch jobs from the
customers, so we just can't fix a web interface and be done with it :(

The logic flow as the Order is inserted
T1) sub tables are updated for order
T2) correction for 1 B2B client in fixing their data to work in our system
T3) Fix columns:
        -- Does this B2B have any biz rules?
        -- See if this is a repeate order (prior orders?)
        -- Apply rules depending on repeat or not. (column in our bizRule
table)

If I have to enforce 2 rules from BizRules
SHIPMETHOD 'USPS'
CLUB 'PRIORITYB'

What I want to do is:
Update Orders
Set shipmenthod = 'USPS', club ='PRIORITYB'
where orderID = @order

Would it be prudent to iterate through a cursor for 2 to 5 rows to create an
@SET instead?

set @SET = @SET + @colBiz +'= '''+@valBiz

then do this instead:
Update Orders
Set @SET
where orderID = @order

Can that text be executed within a trigger or combined into a string and
then execute that dynamic string like in a SP?

-------------------

Show quoteHide quote

Unknown Columns!
Simple query?
Can someone explain why this is not an error?
order by
scramble password