DELIMITER |

CREATE TRIGGER testcdr BEFORE INSERT ON cdr
  FOR EACH ROW 
 BEGIN	
		
	declare norows int default 0;
	declare nolen int default 1;
	declare numstr,numstring,temp int default 0;
	

	if new.Flag='1' Then
	
	set NEW.Chargable_Min=CEIL(NEW.Usage_Sec / 60);
		
	set numstr = SUBSTRING(NEW.Called_No,1,((LENGTH(New.Called_No))- 3));
	
		
	while (norows < 1 AND nolen > 0) do	
	
		set numstring:=SUBSTRING(numstr,1,((LENGTH(numstr)) -1));
	
	set NEW.Country =(select Country from ratedetails where (Code=numstring) and Card_Type=(select Account_Type from customerdetails where Uid=new.Uid));
	
	set norows := LENGTH(numstring) ;
	
	set nolen  := LENGTH(numstr);
	
	end while;

	 if norows='1' then
		
		set NEW.Country =(select Country from ratedetails where (Code=numstring) and Card_Type=(select Account_Type from customerdetails where Uid=new.Uid));
	
	   END IF;
	  
	
	
	
	END IF;
	
END;
|
DELIMITER ;



________________________________________________________

insert into cdr values('3','','','','voip3','18158256','ser-in','"voip"<voip>','SIP/ast.d2visp.com-08121180','SIP/isphone-e9dc','Dial','SIP/18158340674@isphone|30','2008-02-01 17:58:52','60','30','ANSWERED','0','2008-02-01 17:57:52','0','0.00','0.00','0','1');

_________________________________________________________________________________________
mysql> select * from cdr;

| Serial_No | uniqueid | userfield | accountcode | Uid   | Called_No   | dcontex
t | clid         | channel                     | dstchannel       | lastapp | la
stdata                   | Start_Time          | duration | Usage_Sec | disposit
ion | amaflags | End_Time            | Country | Rate  | Cost | Chargable_Min |
Flag |
+-----------+----------+-----------+-------------+-------+-------------+--------
--+--------------+-----------------------------+------------------+---------+---
-------------------------+---------------------+----------+-----------+---------
----+----------+---------------------+---------+-------+------+---------------+-
-----+
|         1 |          |           |             | voip  | 18158340674 | ser-in
  | "voip"<voip> | SIP/ast.d2visp.com-08121180 | SIP/isphone-e9dc | Dial    | SI
P/18158340674@isphone|30 | 2008-02-01 17:58:52 |       60 |        30 | ANSWERED
    |        0 | 2008-02-01 17:57:52 | 0       |  0.00 | 0.00 |             0 |
   1 |
|         2 |          |           |             | voip2 | 18158340674 | ser-in
  | "voip"<voip> | SIP/ast.d2visp.com-08121180 | SIP/isphone-e9dc | Dial    | SI
P/18158340674@isphone|30 | 2008-02-01 17:58:52 |       60 |        30 | ANSWERED
    |        0 | 2008-02-01 17:57:52 | USA     | 15.00 | 7.50 |             1 |
   1 |
|         3 |          |           |             | voip3 | 18158256    | ser-in
  | "voip"<voip> | SIP/ast.d2visp.com-08121180 | SIP/isphone-e9dc | Dial    | SI
P/18158340674@isphone|30 | 2008-02-01 17:58:52 |       60 |        30 | ANSWERED
    |        0 | 2008-02-01 17:57:52 | NULL    |  0.00 | 0.00 |             1 |
   1 |
+-----------+----------+-----------+-------------+-------+-------------+--------
--+--------------+-----------------------------+------------------+---------+---
-------------------------+---------------------+----------+-----------+---------
----+----------+---------------------+---------+-------+------+---------------+-
-----+
3 rows in set (0.01 sec)