mysql

Mysql, mysqli, pdo

There are (more than) three popular ways to use MySQL from PHP.

1. The mysql functions are procedural and use manual escaping.

2. mysqli is a replacement for the mysql functions, with object-oriented and procedural versions. It has support for prepared statements.

3. PDO (PHP Data Objects) is a general database abstraction layer with support for MySQL among many other databases. It provides prepared statements, and significant flexibility in how data is returned.

I would recommend using PDO with prepared statements. It is a well-designed API and will let you more easily move to another database (including any that supports ODBC) if necessary.

Store procedure:

Stored Procedures Advantages

§ Stored procedures help increase the performance of application using them. Once created, stored procedures are compiled and stored in the database catalog. A stored procedure typically runs faster than uncompiled SQL statements that are sent from external applications.

§ Stored procedures reduce the traffic between application and database server because instead of sending multiple uncompiled lengthy SQL statements, the application only has to send the stored procedure’s name with parameters.

§ Stored procedures are reusable and transparent to any applications. Stored procedures expose the database interface to all applications so developers don’t have to develop functions that are already supported in stored procedure in those applications.

§ Stored procedures are secured. Database administrator can grant appropriate permission to application that access stored procedures in database catalog without giving any permission on the underlying database tables.

Stored Procedures Disadvantages

§ Stored procedures make the database server high load in both memory and processors. Instead of being focused on the storing and retrieving data, you could be asking the database server to perform a number of logical operations which is not well-designed for database server.

Creation: Update user

DELIMITER $$
DROP PROCEDURE IF EXISTS `test`.`update_user`$$
CREATE PROCEDURE `test`.`update_user`
(
IN userId INT,
IN firstName VARCHAR(100),
IN lastName VARCHAR(100)
)
BEGIN
update users set first_name=firstName,last_name=lastName where users_id=userId;
END $$
DELIMITER ;

Call the store procedure with php:

$rs = $mysqli->query( 'CALL update_user(4,"Rakesh","mumtaz")' );

MySQL triggers

In MySQL, a trigger is a set of SQL statements that is invoked automatically when a change is made to the data on the associated table. A trigger can be defined to be invoked either BEFORE or AFTER data change by INSERT, UPDATE and DELETE statements. MySQL allows you to define maximum six triggers for each table.

§ BEFORE INSERT

§ AFTER INSERT

§ BEFORE UPDATE

§ AFTER UPDATE

§ BEFORE DELETE

§ AFTER DELETE

Creating a Trigger

We now require two triggers:

When a record is INSERTed into the blog table, we want to add a new entry into the audit table containing the blog ID and a type of ‘NEW’ (or ‘DELETE’ if it was deleted immediately).
When a record is UPDATEd in the blog table, we want to add a new entry into the audit table containing the blog ID and a type of ‘EDIT’ or ‘DELETE’ if the deleted flag is set.

Note that the changetime field will automatically be set to the current time.
Each trigger requires:

A unique name. I prefer to use a name which describes the table and action, e.g. blog_before_insert or blog_after_update.
The table which triggers the event. A single trigger can only monitor a single table.
When the trigger occurs. This can either be BEFORE or AFTER an INSERT, UPDATE or DELETE. A BEFORE trigger must be used if you need to modify incoming data. An AFTER trigger must be used if you want to reference the new/changed record as a foreign key for a record in another table.
The trigger body; a set of SQL commands to run. Note that you can refer to columns in the subject table using OLD.col_name (the previous value) or NEW.col_name (the new value). The value for NEW.col_name can be changed in BEFORE INSERT and UPDATE triggers.

The basic trigger syntax is:

CREATE

    TRIGGER `event_name` BEFORE/AFTER INSERT/UPDATE/DELETE

    ON `database`.`table`

    FOR EACH ROW BEGIN

               -- trigger body

               -- this code is applied to every

               -- inserted/updated/deleted row

    END;

We require two triggers — AFTER INSERT and AFTER UPDATE on the blog table. It’s not necessary to define a DELETE trigger since a post is marked as deleted by setting it’s deleted field to true.
The first MySQL command we’ll issue is a little unusual:

DELIMITER $$

Our trigger body requires a number of SQL commands separated by a semi-colon (;). To create the full trigger code we must change delimiter to something else — such as $$.
Our AFTER INSERT trigger can now be defined. It determines whether the deleted flag is set, sets the @changetype variable accordingly, and inserts a new record into the audit table:

CREATE

        TRIGGER `blog_after_insert` AFTER INSERT

        ON `blog`

        FOR EACH ROW BEGIN

               IF NEW.deleted THEN

                       SET @changetype = 'DELETE';

               ELSE

                       SET @changetype = 'NEW';

               END IF;

               INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);

    END$$

Finally, we set the delimiter back to a semi-colon:

DELIMITER;

The AFTER UPDATE trigger is almost identical:

DELIMITER $$

CREATE

        TRIGGER `blog_after_update` AFTER UPDATE

        ON `blog`

        FOR EACH ROW BEGIN

               IF NEW.deleted THEN

                       SET @changetype = 'DELETE';

               ELSE

                       SET @changetype = 'EDIT';

               END IF;

               INSERT INTO audit (blog_id, changetype) VALUES (NEW.id, @changetype);

    END$$

DELIMITER ;

It’s beyond the scope of this article, but you could consider calling a single stored procedure which handles both triggers.

Trigger Happy?

Let’s see what happens when we insert a new post into our blog table:

INSERT INTO blog (title, content) VALUES ('Article One', 'Initial text.');

A new entry appears in the `blog` table as you’d expect:

id	title	content	deleted
1	Article One	Initial text	0

In addition, a new entry appears in our `audit` table:

id	blog_id	changetype	changetime
1	1	NEW	2011-05-20 09:00:00

Let’s update our blog text:

UPDATE blog SET content = 'Edited text' WHERE id = 1;

As well as changing the post, a new entry appears in the `audit` table:

id	blog_id	changetype	changetime
1	1	NEW	2011-05-20 09:00:00
2	1	EDIT	2011-05-20 09:01:00

Finally, let’s mark the post as deleted:

UPDATE blog SET deleted = 1 WHERE id = 1;

The `audit` table is updated accordingly and we have a record of when changes occurred:

id	blog_id	changetype	changetime
1	1	NEW	2011-05-20 09:00:00
2	1	EDIT	2011-05-20 09:01:00
3	1	DELETE	2011-05-20 09:03:00

This is a simple example but I hope it’s provided some insight into the power of MySQL triggers. In my next post we’ll implement a scheduled event to archive deleted posts.

Trigger query sql

USE [doc0nedb]

/****** Object: Trigger [dbo].[manualtbbom] Script Date: 03/15/2013 07:50:33 ******/

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

ALTER TRIGGER [dbo].[manualtbbom]

ON [dbo].[tbBOM]

AFTER INSERT

BEGIN

DECLARE @cdpkid int

DECLARE @bomdwgnumber varchar(255)

SELECT @cdpkid = [ChildDocumentPKId],@bomdwgnumber = [BOMDwgNum] FROM INSERTED

IF(@cdpkid IS NULL OR @cdpkid = '')

BEGIN

DECLARE @childdocpkid int

DECLARE @picnumber int

DECLARE @childissue int

DECLARE @childsizes varchar(2)

DECLARE @childtypes varchar(10)

SET @childdocpkid = (SELECT PKId FROM dbo.tbstorlib WHERE DrawingID = @bomdwgnumber)

SET @picnumber = (SELECT MaterialNumber FROM dbo.tbstorlib where DrawingID = @bomdwgnumber)

SET @childissue = (SELECT Issue FROM dbo.tbstorlib where DrawingID = @bomdwgnumber)

SET @childsizes = (SELECT Size FROM dbo.tbstorlib where DrawingID = @bomdwgnumber)

SET @childtypes = (SELECT DwgType FROM dbo.tbstorlib where DrawingID = @bomdwgnumber)

UPDATE dbo.tbBOM SET ChildDocumentPKId = @childdocpkid, PicNum = @picnumber, ChildIssue = @childissue, ChildSize = @childsizes, ChildType = @childtypes WHERE BOMDwgNum = @bomdwgnumber

END

SIVA BLOG - Web Development

Monday, May 20, 2013

mysql

MySQL triggers

Creating a Trigger

Trigger Happy?

Trigger query sql

No comments:

Post a Comment