Mysql – Dynamically records not getting to cursor in trigger

cursorsMySQLtrigger

I am using cursor in trigger in MySQL. I want to get records of recently added id. I am using this:

CREATE DEFINER=`root`@`localhost` TRIGGER `calculate_property_ratings` AFTER INSERT ON `node` FOR EACH ROW BEGIN
DECLARE property_id integer;
DECLARE ratings integer;
DECLARE cur CURSOR FOR SELECT field_rating_rating, field_property_detail_target_id from field_data_field_rating as fr JOIN field_data_field_property_review as pr on pr.field_property_review_target_id = fr.entity_id JOIN field_data_field_property_detail as pd on pd.entity_id = pr.entity_id WHERE pr.field_property_review_target_id = NEW.nid;

DECLARE EXIT HANDLER FOR NOT FOUND BEGIN END;
call dolog(concat('node id-->', NEW.nid));
IF(NEW.type = 'review') THEN
    call dolog(concat('type-->', NEW.type));
    OPEN cur;
        ins_loop: LOOP
            FETCH cur INTO ratings, property_id;
            call dolog(concat('ratings -->', ratings));
            call dolog(concat('property_id -->', property_id));
            IF NOT(EXISTS(SELECT 1 FROM average_rating_property WHERE (pid = property_id))) THEN
              INSERT INTO average_rating_property (pid,total_ratings,total_count) VALUES (property_id, ratings, 1);
            ELSE
              UPDATE average_rating_property SET total_ratings = total_ratings + ratings AND total_count = total_count + 1  WHERE pid = property_id;
                call dolog('at update');
            END IF;
        END LOOP;
    CLOSE cur;
END IF;
END

If I provide static value instead of NEW.nid at line no.4 then I am getting records and loop is working, else loop is not working. It means NEW.nid is not working in cursor. But it is working while logging at line no. 7.

Is there any mistake in my code?

Best Answer

Don't use a CURSOR in a Trigger.

Do use INSERT ... ON DUPLICATE KEY UPDATE ... instead of your select+insert/update.

IODKU can handle multiple rows at once, but I don't see why that would happen. Perhaps Cursor's SELECT belongs there?

A typical pattern is:

INSERT INTO tbl (...)
    SELECT ...
    ON DUPLICATE KEY UPDATE
        a = VALUES(a), ...

Related Solutions

Sql-server – Slow deletion of records when a trigger is enabled

The row-versioning framework introduced in SQL Server 2005 is used to support a number of features, including the new transaction isolation levels READ_COMMITTED_SNAPSHOT and SNAPSHOT. Even when neither of these isolation levels are enabled, row-versioning is still used for AFTER triggers (to facilitate generation of the inserted and deleted pseudo-tables), MARS, and (in a separate version store) online indexing.

As documented, the engine may add a 14-byte postfix to each row of a table that is versioned for any of these purposes. This behaviour is relatively well-known, as is the addition of the 14-byte data to every row of an index that is rebuilt online with a row-versioning isolation level enabled. Even where the isolation levels are not enabled, one extra byte is added to non-clustered indexes only when rebuilt ONLINE.

Where an AFTER trigger is present, and versioning would otherwise add 14 bytes per row, an optimization exists within the engine to avoid this, but where a ROW_OVERFLOW or LOB allocation cannot occur. In practice, this means the maximum possible size of a row must be less than 8060 bytes. In calculating maximum possible row sizes, the engine assumes for example that a VARCHAR(460) column could contain 460 characters.

The behaviour is easiest to see with an AFTER UPDATE trigger, though the same principle applies to AFTER DELETE. The following script creates a table with a maximum in-row length of 8060 bytes. The data fits on a single page, with 13 bytes of free space on that page. A no-op trigger exists, so the page is split and versioning information added:

USE Sandpit;
GO
CREATE TABLE dbo.Example
(
    ID          integer NOT NULL IDENTITY(1,1),
    Value       integer NOT NULL,
    Padding1    char(42) NULL,
    Padding2    varchar(8000) NULL,

    CONSTRAINT PK_Example_ID
    PRIMARY KEY CLUSTERED (ID)
);
GO
WITH
    N1 AS (SELECT 1 AS n UNION ALL SELECT 1),
    N2 AS (SELECT L.n FROM N1 AS L CROSS JOIN N1 AS R),
    N3 AS (SELECT L.n FROM N2 AS L CROSS JOIN N2 AS R),
    N4 AS (SELECT L.n FROM N3 AS L CROSS JOIN N3 AS R)
INSERT TOP (137) dbo.Example
    (Value)
SELECT
    ROW_NUMBER() OVER (ORDER BY (SELECT 0))
FROM N4;
GO
ALTER INDEX PK_Example_ID 
ON dbo.Example 
REBUILD WITH (FILLFACTOR = 100);
GO
SELECT
    ddips.index_type_desc,
    ddips.alloc_unit_type_desc,
    ddips.index_level,
    ddips.page_count,
    ddips.record_count,
    ddips.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Example', N'U'), 1, 1, 'DETAILED') AS ddips
WHERE
    ddips.index_level = 0;
GO
CREATE TRIGGER ExampleTrigger
ON dbo.Example
AFTER DELETE, UPDATE
AS RETURN;
GO
UPDATE dbo.Example
SET Value = -Value
WHERE ID = 1;
GO
SELECT
    ddips.index_type_desc,
    ddips.alloc_unit_type_desc,
    ddips.index_level,
    ddips.page_count,
    ddips.record_count,
    ddips.max_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID(), OBJECT_ID(N'dbo.Example', N'U'), 1, 1, 'DETAILED') AS ddips
WHERE
    ddips.index_level = 0;
GO
DROP TABLE dbo.Example;

The script produces the output shown below. The single-page table is split into two pages, and the maximum physical row length has increased from 57 to 71 bytes (= +14 bytes for the row-versioning information).

Update example

DBCC PAGE shows that the single updated row has Record Attributes = NULL_BITMAP VERSIONING_INFO Record Size = 71, whereas all other rows in the table have Record Attributes = NULL_BITMAP; record Size = 57.

The same script, with the UPDATE replaced by a single row DELETE produces the output shown:

DELETE dbo.Example
WHERE ID = 1;

Delete example

There is one fewer row in total (of course!), but the maximum physical row size has not increased. Row versioning information is only added to rows needed for the trigger pseudo-tables, and that row was ultimately deleted. The page split remains, however. This page-splitting activity is responsible for the slow performance observed when the trigger was present. If the definition of the Padding2 column is changed from varchar(8000) to varchar(7999), the page no longer splits.

Also see this blog post by SQL Server MVP Dmitri Korotkevitch, which also discusses the impact on fragmentation.

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

What you presented is essentially the correct way for interrupting triggers in MySQL 5.1

I wrote about this before

Dec 23, 2011 : check constraint does not work?
Apr 25, 2011 : Trigger in MySQL to prevent insertion

You cannot do Dynamic SQL in triggers.

You may have to resort to coding the my_signal like this:

CREATE PROCEDURE `my_signal`(error_type INT)
BEGIN
    IF error_type = 1 THEN
        UPDATE `Error: can not insert new OWNER !` set x=1;
    END IF;
    IF error_type = 2 THEN
        UPDATE `Error: Row can not reference itself!` set x=1;
    END IF;
    .
    .
END//

then change the trigger to look like this:

DELIMITER $$
CREATE
  TRIGGER `employee_before_insert` BEFORE INSERT
    ON `Employee`
    FOR EACH ROW BEGIN
      CASE
       WHEN NEW.designation = 'OWNER'  THEN
          CALL my_signal(1);
       WHEN NEW.SSN = NEW.MSSN THEN
          CALL my_signal(2);
      END CASE; 
  END$$   
DELIMITER ;

Sorry for the clumsy programming but Signal Processing is MySQL 5.1 is horrible.

Best Answer

Related Solutions

Sql-server – Slow deletion of records when a trigger is enabled

Mysql – How to write signal function in MySQL that can be called from Triggers and Stored Functions

Related Question