Results 1 to 7 of 7
  1. #1
    Join Date
    Jul 2011
    Posts
    3

    Red face Unanswered: Error in create trigger in mysql

    I have create a trigger in SQL server, the code can function well

    but when i paste the same code in MySql, It found that got lots of errors,
    but i do not know what the error, why cant create successful

    the code show below, can anyone help me

    Code:
    CREATE TRIGGER  New_User
    ON  tblAdmin
    AFTER INSERT 
    AS
    
    DECLARE
    @AdminRECID uniqueidentifier,
    @CreateUser varchar(50),
    @CreateDate datetime,
    @AuditUser varchar(50),
    @AuditDate datetime,
    @MenuRECID uniqueidentifier
    BEGIN
    SELECT RECID = @AdminRECID,
    	   CreateUser = @CreateUser,
    	   CreateDate = @CreateDate,
    	   AuditUser  = @AuditUser,
    	   AuditDate  = @AuditDate
    FROM tblAdmin;
    
    SELECT RECID = @MenuRECID
    FROM tblMenu;
    
    
    INSERT INTO tblAdmin_Access(RECID,AdminRECID,MenuRECID,Status,CreateUser,CreateDate,AuditUser,AuditDate)
    	VALUES ((newid()),@AdminRECID,@MenuRECID,'A',@CreateUser,@CreateDate,@AuditUser,@AuditDate);
    
    
    END;

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ying7690 View Post
    but when i paste the same code in MySql, It found that got lots of errors
    that's because mysql's trigger syntax is very different from sql server's trigger syntax

    you basically have to re-write it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2011
    Posts
    3

    Red face

    Quote Originally Posted by r937 View Post
    that's because mysql's trigger syntax is very different from sql server's trigger syntax

    you basically have to re-write it
    do you have any sample can let me reference, because i got try several time for it, it still cannot work appropriate

  4. #4
    Join Date
    Jul 2011
    Posts
    3

    Red face Error Code: 1415. Not allowed to return a result set from a trigger"

    I have the following code for trigger
    the syntax message was

    "Error Code: 1415. Not allowed to return a result set from a trigger"

    Code:
    delimiter $$
    
    CREATE TRIGGER  New_User AFTER INSERT ON  tblAdmin
    
    FOR EACH ROW
    BEGIN
    DECLARE _AdminRECID char(36);
    DECLARE _CreateUser varchar(50);
    DECLARE _CreateDate datetime;
    DECLARE _AuditUser varchar(50);
    DECLARE _AuditDate datetime;
    DECLARE _MenuRECID CHAR(36);
    
    SELECT RECID = _AdminRECID,
    	   CreateUser = _CreateUser,
    	   CreateDate = _CreateDate,
    	   AuditUser  = _AuditUser,
    	   AuditDate  = _AuditDate
    FROM tblAdmin;
    
    SELECT RECID = _MenuRECID
    FROM tblMenu;
    
    
    
    	INSERT INTO tblAdmin_Access(RECID,AdminRECID,MenuRECID,Status,CreateUser,CreateDate,AuditUser,AuditDate)
    	VALUES (UUID(),_AdminRECID,_MenuRECID,'A',_CreateUser,CURDATE(),_AuditUser,CURDATE());
    
    
    END;

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ying7690 View Post
    do you have any sample can let me reference
    sure, there are examples here: CREATE TRIGGER
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    threads merged
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    It is not clear what you are trying to achieve with this trigger. Perhaps if you start by providing an explanation we can help?

    For instance, you are reading data from the table tblAdmin and also tblMenu. However, if there are many rows in the tblAdmin are you going to process for each one or just the one that is being inserted?

    Also how do you get the _MenuRECID value? This is from the tblMenu table but if there are again many values in this table should we process each one or just the first one or can we derive the correct value from the previous result from tblAdmin?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •