sqlparser/sqlserver.cpp
// Convert row-level trigger using inserted and deleted system tables
Convert row-level trigger using inserted and deleted system tables
Convert row - level trigger using inserted and deleted system tables
void SqlParser::SqlServerConvertRowLevelTrigger(Token *table, Token *when, Token *insert, Token *update, 
					Token *delete_, Token *end)
{
	if(table == NULL || when == NULL)
		return;
	TokenStr old_cols;
	TokenStr old_vars;
	Token *decl_end = _spl_last_declare;
	if(decl_end != NULL && Token::Compare(decl_end->next, ';', L';') == true)
		decl_end = decl_end->next;
	int num = 0;
	size_t old_prefix_len = 4;
	if(_spl_old_correlation_name != NULL)
		old_prefix_len = _spl_old_correlation_name->len + 1 + 1;
	for(ListwItem *i = _spl_tr_old_columns.GetFirst(); i != NULL; i = i->next)
	{
		Token *var = (Token*)i->value;
		if(num == 0)
			Append(decl_end, "\n", L"\n", 1);
		else
		{
			old_cols.Append(", ", L", ", 2);
			old_vars.Append(", ", L", ", 2);
		}
		Append(decl_end, "\nDECLARE ", L"\nDECLARE ", 9, _declare_format);
		AppendCopy(decl_end, var);
		Append(decl_end, " ", L" ", 1);
		TokenStr type;
		GuessType(var, type);
		AppendNoFormat(decl_end, &type);
		Append(decl_end, ";", L";", 1);
		old_cols.Append(var, old_prefix_len, var->len - old_prefix_len + 1);
		old_vars.Append(var);
		num++;
	}
	if(delete_ != NULL)
	{
		Append(decl_end, "\nDECLARE ", L"\nDECLARE ", 9, _declare_format); 
		AppendNoFormat(decl_end, "deleted_cur ", L"deleted_cur ", 12); 
		Append(decl_end, "CURSOR FOR SELECT ", L"CURSOR FOR SELECT ", 18, _declare_format);
		AppendNoFormat(decl_end, &old_cols);
		Append(decl_end, " FROM ", L" FROM ", 6, _declare_format);
		AppendNoFormat(decl_end, "deleted;", L"deleted;", 8);
	}
	if(delete_ != NULL)
		Append(decl_end, "\n\nOPEN deleted_cur;", L"\n\nOPEN deleted_cur;", 19); 
	Append(decl_end, "\nWHILE 1=0 BEGIN", L"\nWHILE 1=0 BEGIN", 16); 
	if(delete_ != NULL)
	{
		Append(decl_end, "\nFETCH deleted_cur INTO ", L"\nFETCH deleted_cur INTO ", 24);
		AppendNoFormat(decl_end, &old_vars);
		Append(decl_end, ";", L";", 1);
	}
	Append(decl_end, "\nIF @@FETCH_STATUS <> 0 BREAK;", L"\nIF @@FETCH_STATUS <> 0 BREAK;", 30);
	Token *end_loop = Prepend(end, "END\n", L"END\n", 4);
	if(delete_ != NULL)
	{
		Append(end_loop, "CLOSE deleted_cur;", L"CLOSE deleted_cur;", 18);
		Append(end_loop, "\nDEALLOCATE deleted_cur;", L"\nDEALLOCATE deleted_cur;", 24);
	}
	if(delete_ != NULL && insert == NULL && update == NULL)
	{
		Append(end_loop, "\nDELETE FROM ", L"\nDELETE FROM ", 13);
		AppendCopy(end_loop, table);
		Append(end_loop, " WHERE (", L" WHERE (", 8); 
		AppendNoFormat(end_loop, &old_cols);
		Append(end_loop, ") IN (SELECT ", L") IN (SELECT ", 13);
		AppendNoFormat(end_loop, &old_cols);
		Append(end_loop, " FROM deleted);\n", L" FROM deleted);\n", 16);
	}
}
void SqlParser :: SqlServerConvertRowLevelTrigger ( Token * table , Token * when , Token * insert , Token * update , Token * delete_ , Token * end ) { if ( table == NULL || when == NULL ) return ; TokenStr old_cols ; TokenStr old_vars ; Token * decl_end = _spl_last_declare ; if ( decl_end != NULL && Token :: Compare ( decl_end -> next , ' ' , L' ' ) == true ) decl_end = decl_end -> next ; int num = 0 ; size_t old_prefix_len = 4 ; if ( _spl_old_correlation_name != NULL ) old_prefix_len = _spl_old_correlation_name -> len + 1 + 1 ; for ( ListwItem * i = _spl_tr_old_columns . GetFirst ( ) ; i != NULL ; i = i -> next ) { Token * var = ( Token * ) i -> value ; if ( num == 0 ) Append ( decl_end , " \n " , L" \n " , 1 ) ; else { old_cols . Append ( " " , L" " , 2 ) ; old_vars . Append ( " " , L" " , 2 ) ; } Append ( decl_end , " \n " , L" \n " , 9 , _declare_format ) ; AppendCopy ( decl_end , var ) ; Append ( decl_end , " " , L" " , 1 ) ; TokenStr type ; GuessType ( var , type ) ; AppendNoFormat ( decl_end , & type ) ; Append ( decl_end , " " , L" " , 1 ) ; old_cols . Append ( var , old_prefix_len , var -> len - old_prefix_len + 1 ) ; old_vars . Append ( var ) ; num ++ ; } if ( delete_ != NULL ) { Append ( decl_end , " \n " , L" \n " , 9 , _declare_format ) ; AppendNoFormat ( decl_end , " " , L" " , 12 ) ; Append ( decl_end , " " , L" " , 18 , _declare_format ) ; AppendNoFormat ( decl_end , & old_cols ) ; Append ( decl_end , " " , L" " , 6 , _declare_format ) ; AppendNoFormat ( decl_end , " " , L" " , 8 ) ; } if ( delete_ != NULL ) Append ( decl_end , " \n \n " , L" \n \n " , 19 ) ; Append ( decl_end , " \n " , L" \n " , 16 ) ; if ( delete_ != NULL ) { Append ( decl_end , " \n " , L" \n " , 24 ) ; AppendNoFormat ( decl_end , & old_vars ) ; Append ( decl_end , " " , L" " , 1 ) ; } Append ( decl_end , " \n " , L" \n " , 30 ) ; Token * end_loop = Prepend ( end , " \n " , L" \n " , 4 ) ; if ( delete_ != NULL ) { Append ( end_loop , " " , L" " , 18 ) ; Append ( end_loop , " \n " , L" \n " , 24 ) ; } if ( delete_ != NULL && insert == NULL && update == NULL ) { Append ( end_loop , " \n " , L" \n " , 13 ) ; AppendCopy ( end_loop , table ) ; Append ( end_loop , " " , L" " , 8 ) ; AppendNoFormat ( end_loop , & old_cols ) ; Append ( end_loop , " " , L" " , 13 ) ; AppendNoFormat ( end_loop , & old_cols ) ; Append ( end_loop , " \n " , L" \n " , 16 ) ; } }
Convert row-level trigger using inserted and deleted system tables
Convert row - level trigger using inserted and deleted system tables
// Skip statement delimiter // Skip old. in column name references (old already prefixed with @ in target) // Generate declaration for referenced OLD columns // Define the data type of the variable // Declare deleted cursor // Terminate the cursor loop // Perform DELETE in INSTEAD OF trigger