Re: Pre-DML trigger vs Foreign keys

[Anushka Dissanayake]

CREATE TRIGGER Portal20.ins_user_students BEFORE INSERT ON Portal20.user_students
FOR EACH ROW BEGIN
declare validate_user integer;
set validate_user = Portal20.validate_user_id(new.user_id,new.updated_by_emp);
if validate_user = 3 then
signal sqlstate '45000' set message_text='Invalid user_id and updated_by_emp.';
else if validate_user = 1 then
signal sqlstate '45000' set message_text='Invalid user_id.';
else if validate_user = 2 then
signal sqlstate '45000' set message_text='Invalid updated_by_emp.';
end if;
end if;
end if;
END;
function
create function Portal20.validate_user_id(v_user_id integer,v_user_id2 integer)
returns int
not deterministic
reads sql data
begin
declare cnt,cnt2 integer;
declare ret integer default 0;
select count(*) into cnt from Portal20.users where user_id=v_user_id;
select count(*) into cnt2 from Portal20.users where user_id=v_user_id2;
if v_user_id != 0 and
v_user_id != 1 and
v_user_id is not null and
cnt < 1 then
set ret = 1;
end if;
if v_user_id2 != 0 and
v_user_id2 != 1 and
v_user_id2 is not null and
cnt2 < 1 then
set ret = ret + 2;
end if;
return ret;
end/
delimiter ;


Users table is having about - 16900 records

CREATE TABLE `user_students` (
`user_student_id` int(11) NOT NULL,
`user_id` int(11) NOT NULL,
`student_id` int(11) NOT NULL,
`created` datetime DEFAULT NULL,
`updated_by_emp` int(11) DEFAULT NULL,
PRIMARY KEY (`user_student_id`),
KEY `student_id` (`student_id`),
CONSTRAINT `user_students_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `students` (`student_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

user_students - table will gow up to 1.5 million records

following values will be pass to the function.
v_user_id = user_student. user_id
v_user_id2 = user_student.updated_by_emp

Here what it does is whenever someone tries to enter a record to user_students table it is checking user_id , updated_by_emp values on users table and validate.
For me this is quiet bulk and less performance method than a foreign key , but my Senior DBA is highly recommending this method than a foreign key.
Her argument is instead of 2 foreign keys we can get done this from a single query .
Other point she says is underneath method of foreign key also a trigger executed by the server it self.
Can you please update this with the proper technique of FK and trigger and most recommended method to use in this situation?

Thanking you in advance.

AD