SQL TRIGGER

gistfile1.sql

USE `veeva_new`;

DELIMITER $$

DROP TRIGGER IF EXISTS veeva_new.user_au$$
USE `veeva_new`$$
	CREATE DEFINER=`root`@`%` TRIGGER `veeva_new`.`user_au` AFTER UPDATE ON `user` FOR EACH ROW
	BEGIN
	SET @UserVerification=(select reps.veeva_rep_id from pdone.reps where reps.veeva_rep_id=OLD.id);
	IF (@UserVerification>0) then
		SET @UserLastMod=(SELECT reps.lastSyncAt FROM pdone.reps WHERE reps.veeva_rep_id=OLD.id LIMIT 0,1); 
		IF (NEW.lastmodifieddate > @UserLastMod) THEN
			# [Will update lastSyncAT to NEW.lastSyncAt]
			UPDATE pdone.reps 
			SET reps.lastSyncAt = NEW.lastmodifieddate,
			reps.updatedAt=now() WHERE reps.veeva_rep_id=OLD.id; 
		END IF;
	ELSE
		INSERT INTO pdone.reps
		(veeva_rep_id,territories_id,display_name,avatar_url,rep_type,username,first,last,title,bio,phone,email,inactive,lastLoginAt,lastSyncAt,repTokenId,createdAt,updatedAt)
		select
			u.id,
			null as territories_id,
			test.LCAPITAL(CONCAT(u.lastname,' ',u.firstname)) as display_name,
			'default avatar url' as avatar_url,
			'VEEVA' as rep_type,
			u.username,
			u.firstname as first,
			u.lastname as last,
			null as title,
			null as bio,
			null as phone,
			u.email,
			'' as inactive,
			null as lastLoginAt,
			u.lastmodifieddate as lastSyncAt,
			null as repTokenId,
			now() as createdAt,
			now() as updatedAt
		from veeva_new.user as u
		where u.id = OLD.id;
	END IF;
	END$$
DELIMITER ;

USE veeva_new;

DELIMITER $$

DROP TRIGGER IF EXISTS veeva_new.user_ai$$
USE veeva_new$$
CREATE DEFINER = CURRENT_USER TRIGGER veeva_new.user_ai AFTER INSERT ON user FOR EACH ROW
BEGIN
INSERT INTO pdone.reps
(veeva_rep_id,territories_id,display_name,avatar_url,rep_type,username,first,last,title,bio,phone,email,inactive,lastLoginAt,lastSyncAt,repTokenId,createdAt,updatedAt)
select
NEW.id,
null as territories_id,
test.LCAPITAL(CONCAT(NEW.lastname,' ',NEW.firstname)) as display_name,
'default avatar url' as avatar_url,
'VEEVA' as rep_type,
NEW.username,
NEW.firstname as first,
NEW.lastname as last,
null as title,
null as bio,
null as phone,
NEW.email,
'' as inactive,
null as lastLoginAt,
NEW.lastmodifieddate as lastSyncAt,
null as repTokenId,
now() as createdAt,
now() as updatedAt;
END
$$
DELIMITER ;