Trigger Help

[Mike]

I have a trigger (code below). Where do I put in IF
statements to kick off the CASE? I put in a commented out
example of the IF I want to do in the code. Any help
would be appreciated!

ALTER Trigger dbo.CreditLimit_tg
on membership_detail
FOR insert, update
as

Declare @Level2 varchar(10),
@Ship_Customer varchar(8),
@ord varchar(10),
@Credit_Status varchar(6),
@Credit_Limit decimal(20,2)

--Declare @IND varchar(3)

declare cr_limit cursor local for
Select Level2 , order_no, Credit_Status, Credit_Limit
from inserted i, Customer c

open cr_limit

fetch cr_limit into @Level2 , @ord, @Credit_Status,
@Credit_Limit

while (@@fetch_status = 0)
If @level2 = 'STAFF' and @Credit_Limit <= '1500' and
@Credit_Status = 'OK'
begin

print 'Selecting Ship Customer'
select @ship_customer = ship_customer
from membership (nolock)
where order_no = @ord

print 'Ship Customer is '+@ship_customer

--If @level2 = 'STAFF' and @Credit_Limit <= '1500' and
@Credit_Status = 'OK'

update c

set c.credit_limit =
case @level2
when 'STAFF' then 1500
when 'Indv' then 1500
When 'Org' then 2000
when 'jr' then 1500
when 'stu' then 100
when 'sust' then 4000
else 0

end,
c.credit_terms =
'Net 30',

modoper = substring(user,1,9)+'-00'
from customer c
Where Customer = @ship_customer

End

close cr_limit
deallocate cr_limit

[Anith Sen]

I am not sure if you need a cursor here to get it done. Simply use a WHERE
clause to filter out the rows & use a single update like:

UPDATE customer
SET credit_limit =
CASE inserted.Level2
WHEN 'STAFF' THEN 1500
WHEN 'Indv' THEN 1500
WHEN 'Org' THEN 2000
WHEN 'jr' THEN 1500
WHEN 'stu' THEN 100
WHEN 'sust' THEN 4000
ELSE 0
END,
credit_terms = 'Net 30',
modoper = SUBSTRING(user, 1, 9) + '-00'
FROM customer
INNER JOIN membership (NOLOCK)
ON customer.ship_customer = membership.ship_customer
INNER JOIN inserted
ON .... --- use the keycolumns in your join
WHERE .... --- add the required filters

--
- Anith
( Please reply to newsgroups only )

[Mike hendricks]

Thank you for your reply.

There would be several if's though. One for each category.

If @level2 = 'STAFF' and @Credit_Limit <= '1500' and @Credit_Status =
'OK'
Then Case
Else
If @level2 = 'STU' and @Credit_Limit <= '100' and @Credit_Status = 'OK'
Else

Etc...........


*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

[Anith Sen]

You can still use CASE expressions with multiple WHEN clauses or even nest
the CASE expressions like :

CASE WHEN inserted.Level2 = 'STAFF'
AND inserted.Credit_Limit <= 1500
AND inserted.Credit_Status = 'OK'
THEN CASE ...

--
- Anith
( Please reply to newsgroups only )