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

More information