Products
Ozeki 10
SMS
Ozeki NG SMS Gateway
SMS Gateway
Message Server 6
On-line manual
Introduction
SMS technology
MMS technology
About
Product features
Installation guide
User guide
Developers guide
Case studies
Business
SMS - E-mail forwarding
Finance
Schools
Entertainment
Application provider
Forwarder
Other
Linux PHP SMS
Credit limits
E-mail SMS Gateway
Appendix
Download
Home
Support
How to buy
SMS Wall
IP SMS Gateway
Android SMS
Excel SMS
Bulk Messenger
Cluster
Developer tools
Robots
Hardware
Technology
Company


On-line manual:
  Explore all the features in this On-line HTML documentation of Ozeki Message Server 6.

Product guide:
  Download Ozeki Message Server 6 Manual in PDF format. This PDF guide explains all the features of Ozeki Message Server 6.
Ozeki Message Server 6
Linux PHP SMS Linux PHP SMS | E-mail SMS Gateway E-mail SMS Gateway

Setting up credit limits on MS SQL

This documents gives you the source code of the SQL triggers you can use in your MS SQL server to create a montly credit system. This credit system will make sure only a limited number of messages can be sent out each month.

Create table creditlimit (
  id int IDENTITY(1,1),
  limitname varchar(100),
  limitcount int
);

insert into creditlimit (limitname,limitcount) values ('general','500');

Create table credithistory (
  id int IDENTITY(1,1),
  monthname int,
  yearname int,
  sendcount int
);


alter table ozekimessageout add credits int;
alter table ozekimessageout add insertdate datetime;

create TRIGGER checklimit
on ozekimessageout
for insert
as
   declare @keret int
   select @keret = limitcount from creditlimit where limitname='general'

   declare @insertid int
   select @insertid=id from inserted

   declare @historycount int
   select @historycount = count(*) from credithistory
      where monthname = month(getdate())
      and yearname = year(getdate())

   if (@historycount=0) begin
       insert into credithistory (monthname,yearname,sendcount)
              values (month(getdate()),year(getdate()),'0')
   end

   declare @maradek int
   select @maradek=sendcount from credithistory
      where monthname = month(getdate())
      and yearname = year(getdate())

   if ((@keret-@maradek)<1) begin
       update ozekimessageout
            set status='nocredits',credits=0,insertdate=getdate()
            where id=@insertid;
   end else begin
       update ozekimessageout
            set credits=@keret-@maradek,insertdate=getdate()
            where id=@insertid;
       update credithistory
            set sendcount=sendcount+1
            where  monthname = month(getdate()) and yearname = year(getdate());
   end
go


Copyright © 2000- - Ozeki Ltd | info@ozeki.hu
Home > Products > SMS > Message Server 6 > On-line manual > Case studies > Credit limits
Legal | Privacy policy | Terms of use
Page: 656 | Login | 35.172.217.40