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