Multiple Codes to choose, Need suggestion

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

General SQL Server Forums

New to SQL Server Programming

Multiple Codes to choose, Need suggestion

Author

Topic

asyed01
Starting Member

13 Posts

Posted - 2009-09-03 : 12:04:07

Hello experts,

I’m working on a trigger and need some suggestion from you experts. I’ve made this trigger on insert of a table zOrgAdmin. I’m checking a code TNC1 in another table zDiscount for column CODE and number of limit for column LIMIT. Now for TNC1 limit is 55. Every time if an insert happens in zOrgAdmin I reduce ‘1’ from 55 if the user type is TNCI in table zDISCOUNT. I wrote my code up to here and it works fine. However there is a long range of CODE as follows.
(TCN9884826, TCN8868362, TCN2899269, TCN9835948, TCN8231517, TCN5355958, TCN2786213, TCN3696782, TCN3567364, TCN1851961, TCN9814764, TCN6375451, TCN6222997, TCN7817799, TCN1989946, TCN1284751, TCN4518462, TCN2716694, TCN7624985, TCN9924636, TCN7816133, TCN4568813, TCN8978357)

The problem is I’m not sure how should I check this list of codes as I did for TNC1. Any suggestion or advice will be greatly appreciated.

Thank a lot in advance.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-09-03 : 14:39:20

You didn't post any code, so I have no idea what you did, but try this

WHERE CODE IN
(TCN9884826, TCN8868362, TCN2899269, TCN9835948, TCN8231517, TCN5355958, TCN2786213, TCN3696782, TCN3567364, TCN1851961, TCN9814764, TCN6375451, TCN6222997, TCN7817799, TCN1989946, TCN1284751, TCN4518462, TCN2716694, TCN7624985, TCN9924636, TCN7816133, TCN4568813, TCN8978357)

Jim

Everyday I learn something that somebody else already knew

asyed01
Starting Member

13 Posts

Posted - 2009-09-03 : 16:02:55

My bat,here is the code.

ALTER TRIGGER [dbo].[DISCOUNT_AD]
ON [dbo].[zOrgAdmin]
FOR insert
AS
--Variables declaration
Declare @limit_flg int, @cutoff_date datetime, @issue_limit varchar, @activations varchar
Set @limit_flg = 0

--check for discount code 'TNC1'
select @cutoff_date = cutoff_date,
@issue_limit = issue_limit,
@activations = activations
from dbo.Discount
where discount_code2 = 'TNC1'

if @issue_limit <= 49
set @cutoff_date = GETDATE() - 1
set @issue_limit = @issue_limit - 1
set @activations = @activations + 1
begin
insert into [gpitt].[DISCOUNT_AD_TRIGGER_TEST]
([DISCOUNT_CODE2],[cutoff_Date],[issue_limit],[activations],[limit_flg])
values
('TNC1', @cutoff_date, @issue_limit, @activations, '1')
end

--else
-- begin
-- raiserror ('Issue Limit has reached upto MAXIMUM.',16,1)
-- end

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-09-03 : 16:17:18

First off, why are you implementing this as a trigger? Secondly, your current trigger will only handle singleton inserts.

If you have a list of codes that can change; I'd suggest that you put them into a table or add a column to the existing table to denote that the Limit should be checked. Then you can join to the table and not have to change code of the Codes change.

asyed01
Starting Member

13 Posts

Posted - 2009-09-04 : 09:37:47

Okay this database is running behind a business application which keep records of our members. Furthermore different kind of members have different special offers. These offers are good for only once. There is a CODE and LIMIT associated with every member record. I hope you understand why I need to implement a trigger not a simple table.

Yes I know I’m checking just 1 CODE but that was my question.

asyed01
Starting Member
5 Posts Posted - 09/03/2009 : 12:04:07
Hello experts,

I’m working on a trigger …. However there is a long range of CODE as follows.
(TCN9884826, TCN8868362, TCN2899269, TCN9835948, TCN8231517, TCN5355958, TCN2786213, TCN3696782, TCN3567364, TCN1851961, TCN9814764, TCN6375451, TCN6222997, TCN7817799, TCN1989946, TCN1284751, TCN4518462, TCN2716694, TCN7624985, TCN9924636, TCN7816133, TCN4568813, TCN8978357)

The problem is I’m not sure how should I check this list of codes as I did for TNC1. Any suggestion or advice will be greatly appreciated.

Thank a lot in advance.

I guess jimf suggestion is not bad.

Thanks for your input though.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-09-05 : 06:29:30

so do you have a limit to be checked for each of codes above?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources