while(1)  {
	    lsk=ftell(fmt_lp);
	    if(fgets(buf1,NUM,fmt_lp)==NULL) { 
	      prim[strlen(prim)-1]='\0';
	      strcat(prim,")\n");fputs(prim,sql_lp);fputs("go\n",sql_lp);break; 
	    }
	    sub_space_tab(buf1);
	    if(buf1[0]=='\n')  continue;
	    if(buf1[0]=='[') { 
	      fseek(fmt_lp,lsk,SEEK_SET);
	      prim[strlen(prim)-1]='\0';
	      strcat(prim,")\n");fputs(prim,sql_lp);
	      fputs("go\n",sql_lp);break;  
	    }
	    get_name(buf1,fldname,FLDNAME);
	    get_name(buf1,pkey,PKEY);
	    if(pkey[0]=='y'||pkey[0]=='Y') 
	    strcat(prim,fldname),strcat(prim,",");
	  }
	}
	else { printf("%s format fail\n",table_name);exit(0);}
}

void creat_default(char *table_name)
{
	char buf1[NUM],cc[20];
	char fldname[80],def[80],typelen[20];
	int j,type_flag;

	fgets(buf1,NUM,fmt_lp);
	sub_space_tab(buf1);
	if(buf1[0]=='#')  {
	  while(1)  {
	    lsk=ftell(fmt_lp);
	    if(fgets(buf1,NUM,fmt_lp)==NULL) break;
	    sub_space_tab(buf1);
	    if(buf1[0]=='[') { fseek(fmt_lp,lsk,SEEK_SET);break;  }
	    if(buf1[0]=='\n')  continue;
	    get_name(buf1,fldname,FLDNAME);
	    get_name(buf1,def,DEFAULT);
	    get_name(buf1,typelen,TYPELEN);
	    if(def[0]!='\0'&&def[0]!='\n')      {
	      strncpy(cc,typelen,4);cc[4]='\0';
	      if(stricmp(cc,"char")==0) type_flag=0;
	      else  {
		j=0;while(data_type[j]!=NULL&&stricmp(typelen,data_type[j])!=0) j++;
		if(data_type[j]==NULL)  { printf("%s not found %s type\n",table_name,typelen);exit(0); }
		type_flag=j;
	      }
	      if(type_flag==0) 
	      strcpy(cc,"'"),strcat(cc,def),strcat(cc,"'");
	      else strcpy(cc,def);
	      sprintf(buf1,"execute sp_unbindefault '%s.%s'\n",table_name,fldname);
	      fputs(buf1,sql_lp);
	      fputs("go\n",sql_lp);
	      sprintf(buf1,"if exists (select * from sysobjects where id=object_id('def%s%s'))\n",table_name,fldname);
	      fputs(buf1,sql_lp);
	      fputs("begin\n",sql_lp);
	      sprintf(buf1,"  drop default def%s%s\n",table_name,fldname);
	      fputs(buf1,sql_lp);
	      fputs("end\n",sql_lp);
	      fputs("go\n",sql_lp);
	      sprintf(buf1,"create default def%s%s as %s\n",table_name,fldname,cc);
	      fputs(buf1,sql_lp);
	      fputs("go\n",sql_lp);
	      sprintf(buf1,"execute sp_bindefault def%s%s, '%s.%s'\n",table_name,fldname,table_name,fldname);
	      fputs(buf1,sql_lp);
	      fputs("go\n\n",sql_lp);
	    }
	  }
	}
	else { printf("%s format fail\n",table_name);exit(0);}
}

void creat_rule(char *table_name)
{
	char buf1[NUM];
	char fldname[80],rule[80];

	fgets(buf1,NUM,fmt_lp);
	sub_space_tab(buf1);
	if(buf1[0]=='#')  {
	  while(1)  {
	    lsk=ftell(fmt_lp);
	    if(fgets(buf1,NUM,fmt_lp)==NULL) break;
	    sub_space_tab(buf1);
	    if(buf1[0]=='[') { fseek(fmt_lp,lsk,SEEK_SET);break;  }
	    if(buf1[0]=='\n')  continue;
	    get_name(buf1,fldname,FLDNAME);
	    get_name(buf1,rule,RULE);
	    if(rule[0]!='\0'&&rule[0]!='\n')  {
	      sprintf(buf1,"if exists (select * from sysobjects where type='U' and name='rule%s%s')\n",table_name,fldname);
	      fputs(buf1,sql_lp);
	      fputs("begin\n",sql_lp);
	      sprintf(buf1,"    drop rule rule%s%s\n",table_name,fldname);
	      fputs(buf1,sql_lp);
	      fputs("end\n",sql_lp);
	      fputs("go\n",sql_lp);
	      sprintf(buf1,"create rule rule%s%s as\n",table_name,fldname);
	      fputs(buf1,sql_lp);
	      sprintf(buf1,"       @%s %s\n",fldname,rule);
	      fputs(buf1,sql_lp);
	      fputs("go\n",sql_lp);
	      sprintf(buf1,"execute sp_bindrule rule%s%s,'%s.%s'\n",table_name,fldname,table_name,fldname);
	      fputs(buf1,sql_lp);
	      fputs("go\n",sql_lp);
	    }
	  }
	}
	else { printf("%s format fail\n",table_name);exit(0);}
}

void creat_trigger(char *table_name)
{              
	char buf1[NUM];
	char reftab[80],refcol[80],fkey[80],ftabname[20],fldname[20];
	char father_tab[20][20],son_tab[100][20],father_fld[20][20],son_fld[100][20],son_col[100][20],father_col[20][20];
	int father_flag,son_flag,i,j,k;
	long seek_flag;

	fgets(buf1,NUM,fmt_lp);
	sub_space_tab(buf1);
	seek_flag=ftell(fmt_lp);
	father_flag=0;son_flag=0;
	if(buf1[0]=='#')  {
	  while(1)  {
	    lsk=ftell(fmt_lp);
	    if(fgets(buf1,NUM,fmt_lp)==NULL) break;
	    sub_space_tab(buf1);
	    if(buf1[0]=='[') { fseek(fmt_lp,lsk,SEEK_SET);break;  }
	    if(buf1[0]=='\n')  continue;
	    get_name(buf1,fldname,FLDNAME);
	    get_name(buf1,reftab,REFTAB);
	    get_name(buf1,refcol,REFCOL);
	    get_name(buf1,fkey,FKEY);
	    if(fkey[0]=='y'||fkey[0]=='Y')  {
	      if(reftab[0]=='\0'||refcol[0]=='\0') 
	      { printf("%s fkey is y , but not found reftab or refcol\n",table_name);exit(0);}
	      strcpy(father_tab[father_flag],reftab);
	      strcpy(father_fld[father_flag],fldname);
	      strcpy(father_col[father_flag],refcol);
	      father_flag++;
	    }
	  }
	  father_tab[father_flag][0]=0;
	  father_fld[father_flag][0]=0;
	}
	else { printf("%s format fail\n",table_name);exit(0);}
	fseek(fmt_lp,0L,SEEK_SET);
	son_flag=0;
	while(fgets(buf,NUM,fmt_lp)!=NULL)  {
	  sub_space_tab(buf);
	  if(buf[0]=='[')  {
	    i=1;j=1;
	    while(buf[i]!=']'&&buf[i]!='\0')
	    buf1[i-j]=buf[i],i++;
	    if(buf[i]==']')  {
	      buf1[i-j]='\0';
	      sub_space_tab(buf1);
	      k=0;
	      while(volid_table[k]!=NULL&&stricmp(volid_table[k],buf1)!=0) k++;
	      if(volid_table[k]==NULL&&stricmp(buf1,table_name)!=0) {
		strcpy(ftabname,buf1);
		fgets(buf1,NUM,fmt_lp);
		sub_space_tab(buf1);
		if(buf1[0]=='#')  {
		  while(1)  {
		    lsk=ftell(fmt_lp);
		    if(fgets(buf1,NUM,fmt_lp)==NULL) break;
		    sub_space_tab(buf1);
		    if(buf1[0]=='[') { fseek(fmt_lp,lsk,SEEK_SET);break;  }
		    if(buf1[0]=='\n')  continue;
		    get_name(buf1,fldname,FLDNAME);
		    get_name(buf1,reftab,REFTAB);
		    get_name(buf1,refcol,REFCOL);
		    get_name(buf1,fkey,FKEY);
		    if((fkey[0]=='y'||fkey[0]=='Y')&&stricmp(reftab,table_name)==0)  {
		      if(reftab[0]=='\0'||refcol[0]=='\0')
		      { printf("%s fkey is y , but not found reftab or refcol\n",table_name);exit(0);}
		      strcpy(son_tab[son_flag],ftabname);
		      strcpy(son_fld[son_flag],fldname);
		      strcpy(son_col[son_flag],refcol);
		      son_flag++;
		    }
		  }
		  son_tab[son_flag][0]=0;
		  son_fld[son_flag][0]=0;
		}
		else { printf("%s format fail\n",ftabname);exit(0);}
	      }
	    }
	  }
	}
	sprintf(buf1,"/**  table name [%s] **/\n",table_name);
	fputs(buf1,sql_lp);
	fputs("/**  father number  **/\n",sql_lp);
	father_flag=0;son_flag=0;
	while(father_tab[father_flag][0]!=0)    {
	  sprintf(buf1,"/** [%s][%s]  **/\n",father_tab[father_flag],father_fld[father_flag]);
	  fputs(buf1,sql_lp);
	  father_flag++;
	}
	fputs("/** son number  **/\n",sql_lp);
	while(son_tab[son_flag][0]!=0)  {
	  sprintf(buf1,"/** [%s][%s]  **/\n",son_tab[son_flag],son_fld[son_flag]);
	  fputs(buf1,sql_lp);
	  son_flag++;
	}
	son_flag=0;
	if(son_tab[son_flag][0]!=0)  {
	  sprintf(buf1,"/** delete trigger for [%s] **/\n",table_name);
	  fputs(buf1,sql_lp);
	  sprintf(buf1,"if exists (select * from sysobjects where type='TR' and name='del%s')\n",table_name);
	  fputs(buf1,sql_lp);
	  fputs("begin\n",sql_lp);
	  sprintf(buf1,"   drop trigger del%s\n",table_name);
	  fputs(buf1,sql_lp);
	  fputs("end\n",sql_lp);
	  fputs("go\n\n",sql_lp);
	  sprintf(buf1,"create trigger del%s\n",table_name);
	  fputs(buf1,sql_lp);
	  sprintf(buf1,"on %s\n",table_name);
	  fputs(buf1,sql_lp);
	  fputs("for delete\n",sql_lp);
	  fputs("as\n",sql_lp);
	  while(son_tab[son_flag][0]!=0)  {
	    sprintf(buf1,"if (select count(*) from deleted,%s\n",son_tab[son_flag]);
	    fputs(buf1,sql_lp);
	    sprintf(buf1,"where %s.%s=deleted.%s)>0\n",son_tab[son_flag],son_fld[son_flag],son_col[son_flag]);
	    fputs(buf1,sql_lp);
	    fputs("begin\n",sql_lp);
	    fputs("  rollback transaction\n",sql_lp);
	    sprintf(buf1,"  print 'Cannot delete %s with %s records'\n",son_fld[son_flag],son_tab[son_flag]);
	    fputs(buf1,sql_lp);
	    fputs("end\n",sql_lp);
	    son_flag++;
	  }
	  fputs("go\n\n\n",sql_lp);
	}
	father_flag=0;
	if(father_tab[father_flag][0]!=0)  {
	  sprintf(buf1,"/** update trigger for [%s] **/\n",table_name);
	  fputs(buf1,sql_lp);
	  sprintf(buf1,"if exists (select * from sysobjects where type='TR' and name='upd%s')\n",table_name);
	  fputs(buf1,sql_lp);
	  fputs("begin\n",sql_lp);
	  sprintf(buf1,"   drop trigger upd%s\n",table_name);
	  fputs(buf1,sql_lp);
	  fputs("end\n",sql_lp);
	  fputs("go\n\n",sql_lp);
	  sprintf(buf1,"create trigger upd%s\n",table_name);
	  fputs(buf1,sql_lp);
	  sprintf(buf1,"on %s\n",table_name);
	  fputs(buf1,sql_lp);
	  fputs("for update\n",sql_lp);
	  fputs("as\n",sql_lp);
	  fputs("declare @row int\n",sql_lp);
	  fputs("select @row=@@rowcount\n",sql_lp);
	  while(father_tab[father_flag][0]!=0)  {
	    sprintf(buf1,"if update(%s)\n",father_fld[father_flag]);
	    fputs(buf1,sql_lp);
	    fputs("begin\n",sql_lp);
	    sprintf(buf1," if (select distinct %s from inserted) is null\n",father_fld[father_flag]);
	    fputs(buf1,sql_lp);
	    fputs("  begin\n",sql_lp);
	    fputs("    rollback transaction\n",sql_lp);
	    fputs("  end\n",sql_lp);
	    fputs("  else\n",sql_lp);
	    sprintf(buf1,"  if (select count(*) from %s,inserted\n",father_tab[father_flag]);
	    fputs(buf1,sql_lp);
	    sprintf(buf1,"  where %s.%s=inserted.%s)!=@row\n",father_tab[father_flag],father_col[father_flag],father_fld[father_flag]);
	    fputs(buf1,sql_lp);
	    fputs("    begin\n",sql_lp);
	    fputs("    rollback transaction\n",sql_lp);
	    sprintf(buf1,"    print 'Some %s do not exist in %s'\n",father_fld[father_flag],father_tab[father_flag]);
	    fputs(buf1,sql_lp);
	    fputs("    end\n",sql_lp);
	    fputs("end\n",sql_lp);
	    father_flag++;
	  }
	  son_flag=0;
	  while(son_tab[son_flag][0]!=0)  {
	    sprintf(buf1,"if update(%s)\n",son_col[son_flag]);
	    fputs(buf1,sql_lp);
	    fputs("begin\n",sql_lp);
	    sprintf(buf1,"  if (select count(*) from %s,deleted where %s.%s=deleted.%s)>0\n",son_tab[son_flag],son_tab[son_flag],son_fld[son_flag],son_col[son_flag]);
	    fputs(buf1,sql_lp);
	    fputs("  begin\n",sql_lp);
	    sprintf(buf1,"    update %s set %s = inserted.%s\n",son_tab[son_flag],son_fld[son_flag],son_col[son_flag]);
	    fputs(buf1,sql_lp);
	    fputs("    from inserted,deleted\n",sql_lp);
	    sprintf(buf1,"    where %s.%s=deleted.%s\n",son_tab[son_flag],son_fld[son_flag],son_col[son_flag]);
	    fputs(buf1,sql_lp);
	    fputs("  end\n",sql_lp);
	    fputs("end\n",sql_lp);
	    son_flag++;
	  }
	  fputs("go\n\n\n",sql_lp);
	}
	father_flag=0;
	if(father_tab[father_flag][0]!=0)  {
	  sprintf(buf1,"/** insert trigger for [%s] **/\n",table_name);
	  fputs(buf1,sql_lp);
	  sprintf(buf1,"if exists (select * from sysobjects where type='TR' and name='ins%s')\n",table_name);
	  fputs(buf1,sql_lp);
	  fputs("begin\n",sql_lp);
	  sprintf(buf1,"   drop trigger ins%s\n",table_name);
	  fputs(buf1,sql_lp);
	  fputs("end\n",sql_lp);
	  fputs("go\n\n",sql_lp);
	  sprintf(buf1,"create trigger ins%s\n",table_name);
	  fputs(buf1,sql_lp);
	  sprintf(buf1,"on %s\n",table_name);
	  fputs(buf1,sql_lp);
	  fputs("for insert\n",sql_lp);
	  fputs("as\n",sql_lp);
	  fputs("declare @row int\n",sql_lp);
	  fputs("select @row=@@rowcount\n",sql_lp);
	  while(father_tab[father_flag][0]!=0)  {
	    sprintf(buf1,"if (select count(*) from %s,inserted\n",father_tab[father_flag]);
	    fputs(buf1,sql_lp);
	    sprintf(buf1,"where %s.%s=inserted.%s)!=@row\n",father_tab[father_flag],father_col[father_flag],father_fld[father_flag]);
	    fputs(buf1,sql_lp);
	    fputs("begin\n",sql_lp);
	    fputs("  rollback transaction\n",sql_lp);
	    sprintf(buf1,"  print 'Some %s not exist in %s'\n",father_fld[father_flag],father_tab[father_flag]);
	    fputs(buf1,sql_lp);
	    fputs("end\n",sql_lp);
	    father_flag++;
	  }
	  fputs("go\n\n\n",sql_lp);
	}
	fseek(fmt_lp,seek_flag,SEEK_SET);
}

void get_name(char *str1,char* str2,int flag_num)
{
	int i,j;

	i=0;j=0;
	while(i<flag_num-1) {
	  while(str1[j]!='|') j++;
	  j++;i++;
	}
	i=j;
	while(str1[j]!='|'&&str1[j]!='\n'&&str1[j]!='\0')
	str2[j-i]=str1[j],j++;
	str2[j-i]='\0';
	sub_space_tab(str2);
}