Mysql – Updating nested rows

mariadbMySQLtrigger

I'm trying with this code:

DELIMITER //
CREATE TRIGGER myTrigger AFTER UPDATE ON cityies
FOR EACH ROW
BEGIN
 if NEW.visitors <=> OLD.visitors THEN
set visit_time = now()
END IF;
END;//
DELIMITER ;

I'm getting this error message:

unknown system variable visit_time

Note: visit_time has DATETIME type.

Best Answer

There are two problems

PROBLEM #1

The visit_time needs to be prefixed with NEW.

PROBLEM #2

It needs to be a BEFORE UPDATE, not AFTER UPDATE because you cannot change values in an AFTER trigger.

Your code should read

DELIMITER //
CREATE TRIGGER myTrigger BEFORE UPDATE ON cityies
FOR EACH ROW
BEGIN
    IF NEW.visitors <> OLD.visitors THEN
        SET NEW.visit_time = now();
    END IF;
END
//
DELIMITER ;

Related Solutions

MySQL using trigger instead of check

You do not need a RETURN 1 because Trigger is a Stored Procedure, not a Stored Function.

If you want to break it on purpose, that's acceptable.

I wrote two posts about how to break a trigger midstream

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

Try this:

CREATE /*!50017 DEFINER = 'root'@'localhost' */ TRIGGER `test`
BEFORE UPDATE ON `apply` 
FOR EACH ROW
BEGIN
    DECLARE dummy INT;

    IF NEW.cname = "hi" THEN          
        SET NEW.cname = "hello";
    ELSE
        SELECT no_such_column INTO dummy
        FROM information_schema.no_such_table;
    END IF;

END; $$

Sql-server – DDL trigger permission Error

Most likely the problem is that testuser does not have the VIEW SERVER STATE server-level permission. That permission (assigned to the Login in the [master] database, not to the User in the database with the DDL Trigger) is required to access the sys.dm_exec_connections DMV. So you need to do one of the following:

GRANT the testuser Login the VIEW SERVER STATE server-level permission, or
Create a Certificate-based Login that is then assigned the VIEW SERVER STATE server-level permission, and then sign the DDL Trigger with that Certificate using ADD SIGNATURE. The steps for this are provided in the follower answer:

What minimum permissions do I need to provide to a user so that it can check the status of SQL Server Agent Service?

From a security stand-point, I would recommend doing the little bit of extra work to handle the Certificate-based Login and signing the Trigger. This approach doesn't grant any actual Login / User any extra permissions, and will apply to any User that has the proper DDL permissions to perform the actions that would cause the DDL Trigger to fire. Also, assuming that testuser is merely a test case and that these permissions will need to apply more broadly in the final system, then you probably don't want to grant VIEW SERVER STATE to all of these Users or even to a Role.

Along these same lines, I thought that Ownership Chaining would take care of the INSERT permission on DBChangesLogs such that the permission did not have to be explicitly granted to any User(s). If this is not the case, then rather than granting this permission to one or more Users -- or certainly better than adding any User to the db_datawriter Role that grants access (and more than just INSERT) to all tables -- you can simply do two additional, simple steps beyond what is already outlined in that other answer to grant this Trigger the ability to insert into the DBChangesLogs table:

(start with the steps outlined in the "What minimum permissions..." answer linked above, the last part of it being to create the Certificate in the database where the DDL Trigger exists)
Create a User from the Certificate-based Login created in those prior steps (or from this same Certificate that is now also in this database -- the User created will be the same in either case)
GRANT this User the INSERT permission on the DBChangesLogs table

CREATE USER [MrDoStuff] FROM LOGIN [MrDoStuff];
GRANT INSERT ON dbo.DBChangesLogs TO [MrDoStuff];

PS: you declare and set the @ip variable. I assume you will use it once this permission issue is settled?

Best Answer

Related Solutions

MySQL using trigger instead of check

Sql-server – DDL trigger permission Error

Related Question