Update with join

[Brad Braswell]

What I have is a "HR_Table" and an "ACL_Table". What I would like is for the ACL_Table to set the ACL-table fields 'physical_keycard, system_username, and company_email' all to default (which is null) where the emp_id has it's emp_status set to anything but 'Current' on the hr_table. I have the following which isn't throwing any errors but isn't acting right. What is happening is when hr_table.emp_status isn't Current everything acts perfect except for the first trigger that is supposed to set keycard to null but is instead setting everything in that column to null instead of just the effected rows. The last two triggers, setting username and email to null, work exactly as they should. I'm confused on why these two work but the first one doesn't since they are written exactly the same (or so I thought).

{code}

drop trigger if exists gatekeeper.alter_keycard;

DELIMITER $$

USE `gatekeeper`$$

CREATE
DEFINER=`root`@`localhost`
TRIGGER `gatekeeper`.`alter_keycard`
AFTER UPDATE ON `gatekeeper`.`hr_table`
FOR EACH ROW
BEGIN
IF NEW.emp_status NOT LIKE 'Current'
THEN
UPDATE gatekeeper.acl_table, gatekeeper.hr_table

SET acl_table.physical_keycard=default

WHERE (hr_table.emp_id=acl_table.emp_id)

AND (new.emp_status NOT LIKE 'Current');
END IF;
END$$


USE `gatekeeper`$$
drop trigger if exists gatekeeper.alter_username$$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `gatekeeper`.`alter_username`
AFTER UPDATE ON `gatekeeper`.`acl_table`
FOR EACH ROW
BEGIN
IF NEW.physical_keycard = null
THEN
UPDATE gatekeeper.acl_table, gatekeeper.hr_table

SET acl_table.system_username=default

WHERE (hr_table.emp_id=acl_table.emp_id)

AND (new.physical_keycard = null);
END IF;
END$$

USE `gatekeeper`$$

drop trigger if exists gatekeeper.alter_email $$
CREATE
DEFINER=`root`@`localhost`
TRIGGER `gatekeeper`.`alter_email`
AFTER UPDATE ON `gatekeeper`.`acl_table`
FOR EACH ROW
BEGIN
IF new.system_username = null
THEN
UPDATE gatekeeper.acl_table, gatekeeper.hr_table

SET acl_table.company_email=default

WHERE (hr_table.emp_id=acl_table.emp_id)

AND (new.system_username = null);
END IF;
END$$


delimiter ;

{/code}


This thing is haunting me and making me dream in SQL. Someone help me get this working, please!! Thanks in advance



Edited 3 time(s). Last edit at 03/02/2011 07:18PM by Brad Braswell.