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 | Terms of use | 656
3.236.59.63 | 92.118.27.157 | Login