Solved

SQL Anywhere 9, Some records are deleted, we don't know why.

Posted on 2006-11-15
6
323 Views
Last Modified: 2012-05-05
Experts,

The database deleted (for some weirdo reason) some records from table Blog_post.
This happend twice and exatly the same records where deleted, which I resinserted again from a backup.
1) Is there a way to see all the transactions (or sql-statments) in a log file or can I switch such a thing (setting) on?
2) Is it possible that the database is corrupted?
3) Or... suggestions?
0
Comment
Question by:johanreynaert
6 Comments
 
LVL 24

Expert Comment

by:Joe Woodhouse
Comment Utility
1) The transaction log is not human-readable. There are third party products that can read the ASE log - I don't know of any that can read ASA though.

2) Database corruption is always a possibility, but the fact that exactly the same rows were lost makes me think corruption was not your problem.

3) Databases never "just do things". Do you have DELETE triggers? Referential integrity? Or maybe someone sent a bad WHERE clause as part of a DELETE. Human error is your most likely suspect here.
0
 

Author Comment

by:johanreynaert
Comment Utility
I agree with your statement "Databases never just do things". And I think also that I should look for 'The human errror'.
Triggers, no; referential integrity, not for that table.

Meanwhile I added a backup-table and changed my scripting. All delete statements are logged.

I also looked into te database and searched for 'delete from blog_post' (SQL injection) but as you where saying the log-file is not really readable. And I can not search for that string in all tables (since I have around 50 Tables each with their own fields).

Do you know any third party products to read the ASAnywhere log
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
Comment Utility
Sorry, did a bit of searching and I don't see anything that would let you get into the ASA log.

If you don't currently have a delete trigger, you could add one to capture some info if/when a delete occurs...
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Comment

by:johanreynaert
Comment Utility
I have never used triggers, how should I do this.
0
 
LVL 29

Expert Comment

by:leonstryker
Comment Utility
I was not able to assess the ASA users guide, but I got here in Google Cache:

http://72.14.209.104/search?q=cache:mFM7GPGDlhYJ:manuals.sybase.com/onlinebooks/group-sas/awg0703e/dbugen7/%40Generic__BookTextView/36588+asa+sybase+triggers&hl=en&gl=us&ct=clnk&cd=1

Introduction to triggers
You use triggers whenever referential integrity and other declarative constraints are insufficient.

For information on referential integrity, see Ensuring Data Integrity and CREATE TABLE statement .

You may want to enforce a more complex form of referential integrity involving more detailed checking, or you may want to enforce checking on new data but allow legacy data to violate constraints. Another use for triggers is in logging the activity on database tables, independent of the applications using the database.

Trigger execution permissions

Triggers execute with the permissions of the owner of the associated table, not the user ID whose actions cause the trigger to fire. A trigger can modify rows in a table that a user could not modify directly.


Triggers can be defined on one or more of the following triggering actions:

Action
 Description
 
INSERT
 Invokes the trigger whenever a new row is inserted into the table associated with the trigger
 
DELETE
 Invokes the trigger whenever a row of the associated table is deleted.
 
UPDATE
 Invokes the trigger whenever a row of the associated table is updated.
 
UPDATE OF column-list
 Invokes the trigger whenever a row of the associated table is updated such that a column in the column-list has been modified
 

Triggers can be either row-level or statement-level. Row-level triggers execute BEFORE or AFTER each row modified by the triggering insert, update, or delete operation changes. Statement-level triggers execute after the entire operation is performed.

Flexibility in trigger execution time is particularly useful for triggers that rely on referential integrity actions such as cascaded updates or deletes being carried out (or not) as they execute.

If an error occurs while a trigger is executing, the operation that fired the trigger fails. INSERT, UPDATE, and DELETE are atomic operations (see Atomic compound statements ). When they fail, all effects of the statement (including the effects of triggers and any procedures called by triggers) revert back to their pre-operation state.

Creating triggers
You create triggers using either Sybase Central or Interactive SQL. In Sybase Central, you can compose the code in a Code Editor. In Interactive SQL, you can use a CREATE TRIGGER statement. For both tools, you must have DBA or RESOURCE authority to create a trigger and you must have ALTER permissions on the table associated with the trigger.

The body of a trigger consists of a compound statement: a set of semicolon-delimited SQL statements bracketed by a BEGIN and an END statement.

You cannot use COMMIT and ROLLBACK and some ROLLBACK TO SAVEPOINT statements within a trigger.

For more information, see the list of cross-references at the end of this section.

To create a new trigger for a given table (Sybase Central):
Open the Triggers folder of the desired table.

In the right pane, double-click Add Trigger.

Follow the instructions of the wizard.

When the wizard finishes and opens the Code Editor for you, complete the code of the trigger.

To execute the code in the database, choose File-->Save/Execute in Database.

To create a new trigger for a given table (SQL):
Connect to a database.

Execute a CREATE TRIGGER statement.

Example 1: A row-level INSERT trigger
The following trigger is an example of a row-level INSERT trigger. It checks that the birthdate entered for a new employee is reasonable:


CREATE TRIGGER check_birth_date
      AFTER INSERT ON Employee
REFERENCING NEW AS new_employee
FOR EACH ROW
BEGIN
      DECLARE err_user_error EXCEPTION
      FOR SQLSTATE '99999';
      IF new_employee.birth_date > 'June 6, 1994' THEN
            SIGNAL err_user_error;
      END IF;
END

This trigger fires after any row is inserted into the employee table. It detects and disallows any new rows that correspond to birth dates later than June 6, 1994.

The phrase REFERENCING NEW AS new_employee allows statements in the trigger code to refer to the data in the new row using the alias new_employee.

Signaling an error causes the triggering statement, as well as any previous effects of the trigger, to be undone.

For an INSERT statement that adds many rows to the employee table, the check_birth_date trigger fires once for each new row. If the trigger fails for any of the rows, all effects of the INSERT statement roll back.

You can specify that the trigger fires before the row is inserted rather than after by changing the first line of the example to:


CREATE TRIGGER mytrigger BEFORE INSERT ON Employee

The REFERENCING NEW clause refers to the inserted values of the row; it is independent of the timing (BEFORE or AFTER) of the trigger.

You may find it easier in some cases to enforce constraints using declaration referential integrity or CHECK constraints, rather than triggers. For example, implementing the above example with a column check constraint proves more efficient and concise:


CHECK (@col <= 'June 6, 1994')

Example 2: A row-level DELETE trigger example
The following CREATE TRIGGER statement defines a row-level DELETE trigger:


CREATE TRIGGER mytrigger BEFORE DELETE ON employee
REFERENCING OLD AS oldtable
FOR EACH ROW
BEGIN
      ...
END

The REFERENCING OLD clause enables the delete trigger code to refer to the values in the row being deleted using the alias oldtable.

You can specify that the trigger fires after the row is deleted rather than before, by changing the first line of the example to:


CREATE TRIGGER check_birth_date AFTER DELETE ON employee

The REFERENCING OLD clause is independent of the timing (BEFORE or AFTER) of the trigger.

Example 3: A statement-level UPDATE trigger example
The following CREATE TRIGGER statement is appropriate for statement-level UPDATE triggers:


CREATE TRIGGER mytrigger AFTER UPDATE ON employee
REFERENCING NEW AS table_after_update
                        OLD AS table_before_update
FOR EACH STATEMENT
BEGIN
      ...
END

The REFERENCING NEW and REFERENCING OLD clause allows the UPDATE trigger code to refer to both the old and new values of the rows being updated. The table alias table_after_update refers to columns in the new row and the table alias table_before_update refers to columns in the old row.

The REFERENCING NEW and REFERENCING OLD clause has a slightly different meaning for statement-level and row-level triggers. For statement-level triggers the REFERENCING OLD or NEW aliases are table aliases, while in row-level triggers they refer to the row being altered.

For more information, see CREATE TRIGGER statement , and Using compound statements .

Executing triggers
Triggers execute automatically whenever an INSERT, UPDATE, or DELETE operation is performed on the table named in the trigger. A row-level trigger fires once for each row affected, while a statement-level trigger fires once for the entire statement.

When an INSERT, UPDATE, or DELETE fires a trigger, the order of operation is as follows:


BEFORE triggers fire.

Referential actions are performed.

The operation itself is performed.

AFTER triggers fire.


If any of the steps encounter an error not handled within a procedure or trigger, the preceding steps are undone, the subsequent steps are not performed, and the operation that fired the trigger fails.

Altering triggers
You can modify an existing trigger using either Sybase Central or Interactive SQL. You must be the owner of the table on which the trigger is defined, or be DBA, or have ALTER permissions on the table and have RESOURCE authority.

In Sybase Central, you cannot rename an existing trigger directly. Instead, you must create a new trigger with the new name, copy the previous code to it, and then delete the old trigger.

In Interactive SQL, you can use an ALTER TRIGGER statement to modify an existing trigger. You must include the entire new trigger in this statement (in the same syntax as in the CREATE TRIGGER statement that created the trigger).

For information on altering database object properties, see Setting properties for database objects .

To alter the code of a trigger (Sybase Central):
Open the Triggers folder of the desired table.

Right-click the desired trigger.

From the popup menu, do one of the following:


Choose Open as Watcom SQL to edit the code in the Watcom SQL dialect.

Choose Open as Transact SQL to edit the code in the Transact SQL dialect.


In the Code Editor, edit the trigger's code.

To execute the code in the database, choose File-->Save/Execute in Database.

To alter the code of a trigger (SQL):
Connect to the database.

Execute an ALTER TRIGGER statement. Include the entire new trigger in this statement.

For more information, see ALTER TRIGGER statement .

Dropping triggers
Once you create a trigger, it remains in the database until someone explicitly removes it. You must have ALTER permissions on the table associated with the trigger to drop the trigger.

To delete a trigger (Sybase Central):
Open the Triggers folder of the desired table.

Right-click the desired trigger and choose Delete from the popup menu.

To delete a trigger (Sybase Central):
Connect to a database.

Execute a DROP TRIGGER statement.

Example
The following statement removes the trigger mytrigger from the database:


DROP TRIGGER mytrigger

For more information, see DROP statement .

Trigger execution permissions
You cannot grant permissions to execute a trigger, since users cannot execute triggers: Adaptive Server Anywhere fires them in response to actions on the database. Nevertheless, a trigger does have permissions associated with it as it executes, defining its right to carry out certain actions.

Triggers execute using the permissions of the owner of the table on which they are defined, not the permissions of the user who caused the trigger to fire, and not the permissions of the user who created the trigger.

When a trigger refers to a table, it uses the group memberships of the table creator to locate tables with no explicit owner name specified. For example, if a trigger on user_1.Table_A references Table_B and does not specify the owner of Table_B, then either Table_B must have been created by user_1 or user_1 must be a member of a group (directly or indirectly) that is the owner of Table_B. If neither condition is met, a

table not found
message results when the trigger fires.

Also, user_1 must have permissions to carry out the operations specified in the trigger.
0
 
LVL 1

Accepted Solution

by:
-M- earned 125 total points
Comment Utility
If you want to view the ASA log, you can translate it using Sybase's utility.  In Sybase Central, go to the Utilities folder and choose "Translate Log".  There's an option to include the effects of triggers as comments, which could help you in determining whether data was modified via a trigger.  If you prefer the command line approach, you can use the dbtran command.  There are multiple switches available, so I would look up dbtran in the documentation and choose which switches make sense for you.
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SyBase SQL Syntax 7 308
How do we check sybase license in ASE 1 2,467
Sybase IQ how to log personalized Users? 1 190
Clean up a mailbox 5 146
Scam emails are a huge burden for many businesses. Spotting one is not always easy. Follow our tips to identify if an email you receive is a scam.
This article explains how to prepare an HTML email signature template file containing dynamic placeholders for users' Azure AD data. Furthermore, it explains how to use this file to remotely set up a department-wide email signature policy in Office …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now