MySQL Triggers

My recent 'baptism of fire' with MySQL triggers has revealed a couple of differences with their cousins in the SQL Server and Oracle domain.

1) You need seperate triggers for UPDATE, INSERT and DELETE. Oracle and SQL Server will let you combine these into one trigger.

2) You cannot create triggers dynamically using a Procedure. The Execute command in the stored procedures works fine when creating a table, but try creating a Trigger. The parser throws an error stating that you can not run these types of commands.

The real problem for me has been 2). I often rely on writing procedures to apply auditing to a table (composed of audit table and trigger to insert the data from the main table into the audit table).

Creating copies of tables in Oracle, SqlServer and MySQL

As the sub-title of my blog suggests, I am from an Oracle background. But I also have dealings with SQL Server and MySQL databases. In fact the latest project I'm working on is going to have to work with all three databases!

One issue I have encountered recently is how to create copies of tables in SQL Server and MySQL. In Oracle you would use the AS keyword in the CREATE table statement and select from the table that is being copied. For example:

CREATE TABLE myCopyTable AS
SELECT * FROM myOriginalTable;

You can add a WHERE clause to restrict the records that are included in the copy. Making this WHERE clause into a condition that is not achievable (such as WHERE 1 =2) will result in a copy of the table without the data.
Note: This copy does not include any indexes/constraints on the original table.

In MySQL it is similar but you do not need the AS keyword. For example:

CREATE TABLE myCopyTable
SELECT * FROM myOriginalTable;

Alternatively you can actually create an exact copy including all constraints and indexes. This is achieved by using the LIKE keyword.

CREATE TABLE myCopyTable
LIKE myOriginalTable;

Note: This won't take the data across, you would have to insert that afterwards
(e.g. INSERT INTO myCopyTable SELECT * FROM myOriginalTable;)

Finally, SQL Server... which wins the prize for the strangest implementation. You actually use a SELECT command to create your new table :o

SELECT *
INTO myCopyTable
FROM myOriginalTable;

I really don't like the way this is implemented as there is no mention of a table being created in the SQL statement. Even though this is what the user is trying to achieve.
Anyhow, no point complaining at least they provided a way of copying a table :)