Sql For Find Trigger List

SQL Server 2000

SELECT 
     o.name AS trigger_name 
    ,'x' AS trigger_owner 
    /*USER_NAME(o.uid)*/ 
    ,s.name AS table_schema 
    ,OBJECT_NAME(o.parent_obj) AS table_name 
    ,OBJECTPROPERTY(o.id, 'ExecIsUpdateTrigger') AS isupdate 
    ,OBJECTPROPERTY(o.id, 'ExecIsDeleteTrigger') AS isdelete 
    ,OBJECTPROPERTY(o.id, 'ExecIsInsertTrigger') AS isinsert 
    ,OBJECTPROPERTY(o.id, 'ExecIsAfterTrigger') AS isafter 
    ,OBJECTPROPERTY(o.id, 'ExecIsInsteadOfTrigger') AS isinsteadof 
    ,OBJECTPROPERTY(o.id, 'ExecIsTriggerDisabled') AS [disabled] 
FROM sysobjects AS o 
/*
INNER JOIN sysusers 
    ON sysobjects.uid = sysusers.uid 
*/  
INNER JOIN sysobjects AS o2 
    ON o.parent_obj = o2.id 

INNER JOIN sysusers AS s 
    ON o2.uid = s.uid 

WHERE o.type = 'TR'

Ref: http://stackoverflow.com/questions/4305691/need-to-list-all-triggers-in-sql-server-database-with-table-name-and-tables-sch

Trigger for insert or delete record.

Trigger for backup data from sales table of dbPicnPay to Sales table of dbBackUp database. After Delete record from sales table of dbPicnPay that record will insert into Sales table of dbBackUp database.

create trigger TrigSalesHistory on dbPicnPay.dbo.Sales
for delete
as
begin
insert into dbBackUp.dbo.Sales
select * from deleted
end


Trigger for backup data from sales table of dbPicnPay to SalesNew table of dbBackUp database. After Insert record to sales table of dbPicnPay that record will also insert into SalesNew table of dbBackUp database.

create trigger TrigSalesHistory on dbPicnPay.dbo.sales
for insert
as
begin
insert into dbBackUp.dbo.SalesNew
select * from inserted
end




After Insert/Delete record from Sales table of dbPicnPay that operation make failed:

create trigger SalesHistoryS on dbPicnPay.dbo.sales
for delete,insert
as
begin
rollback
end

change mysql file size limit, Execution time limit....

or

Try these different settings in C:\wamp\bin\apache\apache2.2.8\bin\php.ini



Find:
post_max_size = 8M
upload_max_filesize = 2M
max_execution_time = 30
max_input_time = 60
memory_limit = 8M

Change to:
post_max_size = 750M
upload_max_filesize = 750M
max_execution_time = 5000
max_input_time = 5000
memory_limit = 1000M

Then restart wamp to take effect



Thanks.





Contact Us
Email: ritbd.com@gmail.com
Cell: +88 019 12345 565

Simple Encryption & Decryption in C#

//encryption
string output = "";
char[] readChar = InputText.ToCharArray();
for (int i = 0; i < readChar.Length; i++)
{
int no = Convert.ToInt32(readChar[i]) + 10;
string r = Convert.ToChar(no).ToString();
output += r;
}

//decryption

string output = "";
char[] readChar = InputText.ToCharArray();
for (int i = 0; i < readChar.Length; i++)
{
int no = Convert.ToInt32(readChar[i]) - 10;
string r = Convert.ToChar(no).ToString();
output += r;
}

during SQL Server Installation Show error like previous program installation created pending operations

Solution:

a. Click Start, and then click Run.
b. In the Open dialog box, type: "Regedit" (without the quotation marks) or "Regedt32" (without the quotation marks)
c. Click OK.

NOTE: Please make sure that you only delete the value mentioned, not the whole session manager key.
d. In Registry Editor, expand the following registry subkey:HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Session Manager
e. On the File menu, click Export.

NOTE: In Microsoft Windows 2000, click Export Registry File from the Registry menu.
f. In the File name text box, type: "Session Manager Key" (without the quotation marks)
g. Click Save.
h. In the right-pane of the Registry Editor window, right-click PendingFileRenameOperations. On the shortcut menu that appears, click Delete.
i. In the Confirm Value Delete message dialog box that appears, click Yes.
j. On the File menu, click Exit.

NOTE: In Windows 2000, click Exit on the Registry menu.
k. Restart the computer.
l. Using the Registry Editor, verify that the PendingFileRenameOperations registry value is not available.

Note The PendingFileRenameOperations registry value may be re-created when you restart the computer. If the registry values are re-created, delete the PendingFileRenameOperations registry value again by completing steps a through j, and then run SQL Server 2000 Setup. Do not restart the computer before you run SQL Server 2000 Setup.




Ref: http://support.microsoft.com/kb/312995

MySql Trigger

Some Work with MySql Trigger :

 Table Structure 

DROP TABLE IF EXISTS `SchemaName`.`prd_cur_balance`;  
 CREATE TABLE `SchemaName`.`prd_cur_balance` (  
  `id` int(10) unsigned NOT NULL auto_increment,  
  `br_code` varchar(45) NOT NULL default '0',  
  `prd_id` int(10) unsigned NOT NULL default '0',  
  `rate` float NOT NULL default '0',  
  `bqty` float NOT NULL default '0',  
  PRIMARY KEY (`id`)  
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;  
 insert into prd_cur_balance(br_code,prd_id,rate,bqty)  
 SELECT c.br_code,c.prd_id,c.rate,(c.bqty-IFNULL(d.bqty,0)) bqty  
 FROM  
 (SELECT c.br_code,prd_id,rate,sum(qty) bqty FROM challan c, challan_details d where c.id=d.chn_id group by c.br_code,d.prd_id,d.rate) c left outer join  
 (SELECT c.br_code,prd_id,rate,sum(qty) bqty FROM comsumed c, consumed_details d where c.id=d.cons_id group by c.br_code,prd_id,rate) d  
 on c.br_code=d.br_code and c.prd_id=d.prd_id and c.rate=d.rate;  



 DROP TRIGGER trigger_cha_update;  
 DELIMITER $$  
 CREATE TRIGGER trigger_cha_update BEFORE UPDATE ON challan_details  
 FOR EACH ROW  
 BEGIN  
    IF NOT EXISTS(select * from prd_cur_balance where br_code=(select br_code from challan c where c.id=new.chn_id) and prd_id=NEW.prd_id and rate=NEW.rate) THEN  
     insert into prd_cur_balance(br_code,prd_id,rate,bqty)  
     select br_code,new.prd_id,new.rate,new.qty from challan c where c.id=new.chn_id;  
    ELSE  
     UPDATE prd_cur_balance set bqty=bqty-IFNULL(OLD.qty,0) where br_code=(select br_code from challan c where c.id=old.chn_id) and prd_id=old.prd_id and rate=old.rate;  
     UPDATE prd_cur_balance set bqty=bqty+new.qty where br_code=(select br_code from challan c where c.id=new.chn_id) and prd_id=NEW.prd_id and rate=NEW.rate ;  
     delete from prd_cur_balance where bqty=0;  
    END IF;  
 END $$  
 DELIMITER ;  



 DROP TRIGGER trigger_cha_delete;  
 DELIMITER $$  
 CREATE TRIGGER trigger_cha_delete BEFORE DELETE ON challan_details  
 FOR EACH ROW  
 BEGIN  
   UPDATE prd_cur_balance set bqty=bqty-IFNULL(OLD.qty,0) where br_code=(select br_code from challan c where c.id=old.chn_id) and prd_id=old.prd_id and rate=old.rate ;  
   delete from prd_cur_balance where bqty=0;  
 END $$  
 DELIMITER ;  



 DROP TRIGGER trigger_cha_insert;  
 DELIMITER $$  
 CREATE TRIGGER trigger_cha_insert BEFORE INSERT ON challan_details  
 FOR EACH ROW  
 BEGIN  
    IF NOT EXISTS(select * from prd_cur_balance where br_code=(select br_code from challan c where c.id=new.chn_id) and prd_id=NEW.prd_id and rate=NEW.rate) THEN  
     insert into prd_cur_balance(br_code,prd_id,rate,bqty)  
     select br_code,new.prd_id,new.rate,new.qty from challan c where c.id=new.chn_id;  
    ELSE  
     UPDATE prd_cur_balance set bqty=bqty+new.qty where br_code=(select br_code from challan c where c.id=new.chn_id) and prd_id=NEW.prd_id and rate=NEW.rate;  
    END IF;  
 END $$  
 DELIMITER ;  


 DROP TRIGGER trigger_cons_update;  
 DELIMITER $$  
 CREATE TRIGGER trigger_cons_update BEFORE UPDATE ON consumed_details  
 FOR EACH ROW  
 BEGIN  
   UPDATE prd_cur_balance set bqty=bqty+IFNULL(OLD.qty,0) where br_code=(select br_code from comsumed c where c.id=old.cons_id) and prd_id=old.prd_id and rate=old.rate;  
   UPDATE prd_cur_balance set bqty=bqty-new.qty where br_code=(select br_code from comsumed c where c.id=new.cons_id) and prd_id=NEW.prd_id and rate=NEW.rate;  
   -- delete from prd_cur_balance where bqty=0;  
 END $$  
 DELIMITER ;  


 DROP TRIGGER trigger_cons_delete;  
 DELIMITER $$  
 CREATE TRIGGER trigger_cons_delete BEFORE DELETE ON consumed_details  
 FOR EACH ROW  
 BEGIN  
   UPDATE prd_cur_balance set bqty=bqty+IFNULL(OLD.qty,0) where br_code=(select br_code from comsumed c where c.id=old.cons_id) and prd_id=old.prd_id and rate=old.rate ;  
   -- delete from prd_cur_balance where bqty=0;  
 END $$  
 DELIMITER ;  


 DROP TRIGGER trigger_cons_insert;  
 DELIMITER $$  
 CREATE TRIGGER trigger_cons_insert BEFORE INSERT ON consumed_details  
 FOR EACH ROW  
 BEGIN  
   UPDATE prd_cur_balance set bqty=bqty-IFNULL(new.qty,0) where br_code=(select br_code from comsumed c where c.id=new.cons_id) and prd_id=new.prd_id and rate=new.rate ;  
   -- delete from prd_cur_balance where bqty=0;  
 END $$  
 DELIMITER ;  

MySql Procedure with Cursor, Cumulative Sum, Auto Increment Column in Select Line & Procedure Call with a procedure

MySql Procedure with Cursor, Cumulative Sum, Auto Increment Column in Select Line & Procedure Call with a procedure...

Procedure with Cursor

1:  DELIMITER $$  
2:  DROP PROCEDURE IF EXISTS `dbName`.`myProc1` $$  
3:  CREATE PROCEDURE `myProc1`()  
4:  BEGIN  
5:       DECLARE l_id INT;  
6:       DECLARE l_rate INT;  
7:       DECLARE no_more_rate INT;  
8:    DECLARE bal_rate_csr CURSOR FOR  
9:       SELECT distinct rate  
10:       FROM tmp1;  
11:    DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_rate=1;  
12:    DROP TABLE IF EXISTS tmp2;  
13:    create TABLE tmp2 as  
14:    select *,0 bqty from tmp1;  
15:    truncate table tmp2;  
16:    SET no_more_rate=0;  
17:    OPEN bal_rate_csr;  
18:    bal_rate_loop:WHILE(no_more_rate=0) DO  
19:     FETCH bal_rate_csr INTO l_rate;  
20:     IF no_more_rate=1 THEN  
21:       LEAVE bal_rate_loop;  
22:     END IF;  
23:   set @tt=0;  
24:   insert into tmp2(rdate,dsc,prd_id,rec_qty,cons_qty,bal_qty,rate,rowid,row_count,balqty,bqty)  
25:   select *,@tt:=@tt+bal_qty+rec_qty-cons_qty as balqty from tmp1  
26:   where rate=l_rate  
27:   order by row_count;  
28:   END WHILE bal_rate_loop;  
29:   CLOSE bal_rate_csr;  
30:  SET no_more_rate=0;  
31:  END $$  
32:  DELIMITER ;  

Procedure with Cumulative Sum, Auto Increment Column in Select Line & Procedure Call with a procedure

1:  DELIMITER $$  
2:  DROP PROCEDURE IF EXISTS `dbName`.`sp_product_ledger` $$  
3:  CREATE PROCEDURE `sp_product_ledger`(prdid int,FromDate DateTime, ToDate DateTime,brcode varchar(45) )  
4:  BEGIN  
5:   DROP TEMPORARY TABLE IF EXISTS tmp;  
6:   create temporary table tmp as  
7:   /* Opening Qty,Rate,Value */  
8:   select DATE_SUB(FromDate, INTERVAL 1 DAY) rdate,'Carried Over Balance' dsc,r.prd_id,0 rec_qty,0 cons_qty,(r.qty-IFNULL(c.qty,0)) bal_qty,r.rate,0 rowid  
9:   from  
10:   (SELECT d.prd_id,sum(d.qty) qty,d.rate FROM challan c, challan_details d  
11:   where c.br_code=brcode and d.prd_id=prdid and c.id=d.chn_id and c.chn_date<FromDate  
12:   group by d.prd_id,d.rate) r  
13:   left outer join  
14:   (SELECT d.prd_id,sum(d.qty) qty,d.rate FROM comsumed c,consumed_details d  
15:   where d.prd_id=prdid and c.id=d.cons_id and c.cons_date<FromDate  
16:   group by d.prd_id,d.rate) c  
17:   on r.prd_id=c.prd_id and r.rate=c.rate;  
18:  /* Report Month Receive Qty,Rate,Value Start*/  
19:   insert into tmp(rdate,dsc,prd_id,rec_qty,cons_qty,bal_qty,rate,rowid)  
20:   SELECT c.chn_date rdate,'Receive' dsc,d.prd_id prd_id,d.qty rec_qty,0 cons_qty,0 bal_qty,d.rate, '1' rowid  
21:   FROM challan c, challan_details d  
22:   where c.br_code=brcode and d.prd_id=prdid and c.id=d.chn_id and  
23:   c.chn_date>=FromDate and c.chn_date<=ToDate;  
24:  /* Report Month Receive Qty,Rate,Value End*/  
25:  /* Report Month Consumed Qty,Rate,Value Start*/  
26:   insert into tmp(rdate,dsc,prd_id,rec_qty,cons_qty,bal_qty,rate,rowid)  
27:   SELECT c.cons_date rdate,'Issue' dsc,d.prd_id prd_id,0 rec_qty,d.qty cons_qty,0 bal_qty,d.rate, 2 rowid  
28:   FROM comsumed c, consumed_details d  
29:   where c.br_code=brcode and d.prd_id=prdid and c.id=d.cons_id and  
30:   c.cons_date>=FromDate and c.cons_date<=ToDate;  
31:  /* Report Month Receive Qty,Rate,Value End*/  
32:  /*Report Select Query Start */  
33:     /* select prd_id,p.prd_name,qty opn_qty,(qty*rate) opn_amt,rec_qty,cons_qty,rate,(qty+rec_qty-cons_qty) cls_qty,((qty+rec_qty-cons_qty)*rate) cls_amt  
34:      from tmp t,product p  
35:      where t.prd_id=p.id;  
36:      */  
37:   /*DECLARE cur11 CURSOR FOR SELECT rec_qty,cons_qty,bal_qty,rate from tmp ;*/  
38:   set @a=0;  
39:   set @tt=0;  
40:   set @i =0;  
41:   DROP TABLE IF EXISTS tmp1;  
42:   create TABLE tmp1 as  
43:   select *,@a:=@a+1 as row_count,@tt:=@tt+bal_qty+rec_qty-cons_qty as balqty from tmp  
44:   order by rdate,rowid;  
45:    /*  set @tg=0;  
46:     select @tg:=count(*) ff from tmp1;  
47:    WHILE @i < @tg DO  
48:      set @i =@i+1;  
49:      set @tot=0;  
50:      set @ltot=0;  
51:     select @ltot:=sum(bal_qty) from tmp1 where rowid=0 and rate=(select rate from tmp1 where row_count=@i);  
52:      select @tot:=sum(@ltot+sum(rec_qty-cons_qty)) from tmp1 where row_count<=@i  
53:      and rate = (select rate from tmp1 where row_count=@i);  
54:      update tmp1  
55:      set bal_qty = @tot  
56:      where row_count=@i;  
57:    END WHILE;  
58:    */  
59:    CALL myProc1();  
60:    select rdate,dsc,rec_qty,cons_qty,bqty bal_qty,cons_qty*rate debit,rec_qty*rate credit,bqty*rate bal_amt,rate from tmp2  
61:    order by row_count;  
62:  /*Report Select Query EStart   */  
63:  drop temporary table if exists tmp;  
64:  /*drop table if exists tmp1;  
65:  drop table if exists tmp2;*/  
66:  END $$  
67:  DELIMITER ;  

Dynamic DataTable Bind from DataTable after Filtering by any value of DataTable

DataTable Bind Method

1:  public DataTable FilterDataTable(DataTable iDt, string FilterBy) //Desired DataTable  
2:      {  
3:        DataTable ndt = new DataTable();  
4:        int v;  
5:        // Query String are generate here  
6:        string FilterQuery = "";  
7:        for (v = 0; v <= iDt.Columns.Count - 1; v++)  
8:        {  
9:          ndt.Columns.Add(iDt.Columns[v].ColumnName); // Binding Output DataTable Colums  
10:          if (FilterQuery == "")  
11:          {  
12:            FilterQuery = dt.Columns[v].ColumnName + " Like '%" + FilterBy + "%' ";  
13:          }  
14:          else  
15:          {  
16:            FilterQuery += " or " + iDt.Columns[v].ColumnName + " like '%" + FilterBy + "%' ";  
17:          }  
18:        }  
19:        DataRow[] dtr;  
20:        dtr = iDt.Select(FilterQuery); // Query Run  
21:        foreach (DataRow row in dtr)  
22:        {  
23:          DataRow drt = ndt.NewRow();  
24:          for (v = 0; v <= iDt.Columns.Count - 1; v++)  
25:          {  
26:            drt[v] = row[v];  
27:          }  
28:          ndt.Rows.Add(drt); // Binding Output Rows  
29:        }  
30:        return ndt; // Return DataTable after Filtering  
31:      }  

1:  //Calling DataTable  
2:  dataGridView2.DataSource = FilterDataTable(InputDataTable, SearchbyValue);