Sample Header Ad - 728x90

Conditional update

0 votes
1 answer
88 views
I would like to create a conditional update on an audit table, such that if the update on the main table succeeds, then the update on the audit table is performed. Right now I have this
CREATE DEFINER=root@localhost PROCEDURE updateParent(
	IN inId INT(10), 
	IN inName varchar(75), 
	IN inEmail varchar(50), 
	IN inPhone varchar(8),
    IN inUsername varchar(50)
)
BEGIN

SET AUTOCOMMIT = 0;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;

	update parent
    set nameOfPerson = inName
			  ,email = inEmail 
              ,phonenr = inPhone
    where id = inId;
    
COMMIT;           
    
    SET @rowcount = ROW_COUNT();
    
    IF @rowcount > 0 THEN 
		INSERT INTO parent_h
		(parent_id, nameOfPerson, email, phonenr, opts, event, username)
		SELECT id, nameOfPerson, email, phonenr, ts, "U", inUsername
		from parent
		where id = inId;        
	END IF;
    
        
COMMIT;       
    
END
Occasionally, if the update is performed and zero rows are updated (no data was changed), then I do not want the audit table update to be performed. Are there any disadvantages of doing it this way? and are there any better ways? This is a web-application. UPDATE: I tried with an AFTER_UPDATE trigger. It seems like MySQL runs the trigger even if zero rows are updated on the table. That means I need to test each field to determine if any actual changes were made.
CREATE DEFINER=root@localhost TRIGGER parent_AFTER_UPDATE AFTER UPDATE   ON parent FOR EACH ROW BEGIN  

IF (  
	OLD.id  NEW.id or  
	OLD.nameOfPerson  NEW.nameOfPerson or  
	OLD.email  NEW.email or   
	OLD.phonenr  NEW.phonenr  
)   
THEN  
	INSERT INTO parent_h  
		(  
		parent_id,   
		nameOfPerson,   
		email,   
		phonenr,   
		opts,   
		event,   
		username  
		)  
		VALUES   
		(  
		NEW.id,  
		NEW.nameOfPerson, 
		NEW.email, 
		NEW.phonenr, 
		NEW.ts,  
		'U',  
		inUsername
		)  
	  ;  
END IF;  
    
END
An additional issue is that I need to add the userid of the principal performing the update, which is not available in the table update.
Asked by tcelvis (1 rep)
Jun 16, 2024, 05:28 PM
Last activity: Jul 7, 2025, 07:12 AM