创建触发器及函数

1、mysql创建触发器

sql = "CREATE TRIGGER `delkm` AFTER DELETE ON `td_kmdm` FOR EACH ROW begin "
    + " delete from t_pyqx where t_pyqx.kmdm=old.kmdm;"
    + " delete from td_klkmksb where td_klkmksb.kmdm=old.kmdm;"
    + " delete from td_ycdm where td_ycdm.kmdm=old.kmdm;"
    + " delete from t_jmxx where t_jmxx.kmdm=old.kmdm;"
    + " delete from td_jxxxb where td_jxxxb.kmdm=old.kmdm;"
    + " delete from t_stdfd where t_stdfd.kmdm=old.kmdm;"
    + " delete from t_qydyb where t_qydyb.kmdm=old.kmdm;"
    + " delete from t_xzdmb where t_xzdmb.kmdm=old.kmdm;"
    + " delete from t_yhtemp where t_yhtemp.kmdm=old.kmdm;"
    + " delete from td_qxzdm where td_qxzdm.kmdm=old.kmdm;"
    + " end;";s

 

2、mysql创建函数

sql="create function substring_index(Str varchar(8000),StrSep varchar(10),AppPos int) " +
   "returns int " +
   "begin" +
   " declare i int;" +
   " declare ii int;" +
   " set Str=rtrim(ltrim(Str));" +
   " set i=1;" +
   " select charindex(StrSep,Str) into ii;" +
   " if i=AppPos then " +
   " return (ii);" +
   " else" +
   " test:while AppPos>i do " +
   " begin" +
   " if charindex(StrSep,right(Str,len(Str)-ii))<>0 then " +
   " select charindex(StrSep,right(Str,len(Str)-ii))+ii into ii;" +
   " else" +
   " set ii=0;" +
   " set i=i+1;" +
   " end if;" +
   " end;" +
   " end while test;" +
   " end if;" +
   " return (ii );" +
   " end";

 

3、sqlserver创建触发器

sql = "CREATE TRIGGER delks ON td_ksdm for DELETE  as   "
      + " delete t_pyqx from deleted   a,t_pyqx b where a.ksdm=b.ksdm ;"
      + " delete  td_klkmksb from deleted   a,td_klkmksb where td_klkmksb.ksdm=a.ksdm;"
      + " delete td_ycdm from deleted   a, td_ycdm where td_ycdm.ksdm=a.ksdm;"
      + " delete t_jmxx from deleted   a,t_jmxx where t_jmxx.ksdm=a.ksdm;"
      + " delete td_jxxxb from deleted   a,td_jxxxb where td_jxxxb.ksdm=a.ksdm;"
      + " delete t_stdfd from deleted   a,t_stdfd where t_stdfd.ksdm=a.ksdm;"
      + " delete t_qydyb from deleted   a,t_qydyb where t_qydyb.ksdm=a.ksdm;"
      + " delete t_xzdmb from deleted   a,t_xzdmb where t_xzdmb.ksdm=a.ksdm;"
      + " delete t_yhtemp from deleted   a, t_yhtemp where t_yhtemp.ksdm=a.ksdm;"
      + " delete td_qxzdm from deleted   a, td_qxzdm where td_qxzdm.ksdm=a.ksdm;";

 

4、sqlserver创建函数

sql="create function [dbo].[substring_index](@Str varchar(8000),@StrSep varchar(10),@AppPos int) " +
   "returns int " +
   "begin" +
   " declare @i int" +
   " declare @ii int" +
   " set @Str=rtrim(ltrim(@Str))" +
   " set @i=1" +
   " select @ii=charindex(@StrSep,@Str)" +
   " if @i=@AppPos" +
   " return @ii" +
   " else" +
   " while @AppPos>@i" +
   " begin" +
   " if charindex(@StrSep,right(@Str,len(@Str)-@ii))<>0" +
   " select @ii=charindex(@StrSep,right(@Str,len(@Str)-@ii))+@ii" +
   " else" +
   " set @ii=0" +
   " set @i=@i+1" +
   " end" +
   " return @ii " +
   "end";