Question on insert Trigger? Plz Help.

I am beginner of MS SQL, currently work on a school project that require the use of trigger on insert. I am trying to create a trigger that no duplicate last and first name accept. Here is the code for the table:

create table employee(
emp_id numeric(5,0) identity primary key,
job_id numeric(5,0) not null foreign key references job(job_id),
emp_last varchar(20) not null,
emp_first varchar(20) not null,
emp_phone varchar(15) not null,
emp_add varchar(30) not null,
emp_city varchar(20) not null,
emp_state varchar(2) not null,
emp_zip numeric(5,0) not null,
emp_dob smalldatetime NOT NULL default '0000-00-00',
emp_hiredate smalldatetime NOT NULL default '0000-00-00',
emp_payrate numeric(5,2) not null,
total_sales numeric(9,2) not null default 0,
termination varchar(3) not null default 'NO',
termin_date nulldate null)

Here is the code for the trigger:

create trigger trg_add_emp
on employee
for insert as

declare @job_id numeric(5,0),
@emp_last varchar(20),
@emp_first varchar(20),
@emp_phone varchar(15),
@emp_add varchar(30),
@emp_city varchar(20),
@emp_state varchar(2),
@emp_zip numeric(5,0),
@emp_dob datetime,
@emp_hiredate datetime,
@emp_payrate numeric(5,2),
@total_sales numeric(9,2),
@termination varchar(3),
@termin_date datetime

select @job_id=(select job_id from inserted)
select @emp_last=(select emp_last from inserted)
select @emp_first=(select emp_first from inserted)
select @emp_phone=(select emp_phone from inserted)
select @emp_add=(select emp_add from inserted)
select @emp_city=(select emp_city from inserted)
select @emp_state=(select emp_state from inserted)
select @emp_zip=(select emp_zip from inserted)
select @emp_dob=(select emp_dob from inserted)
select @emp_hiredate=(select emp_hiredate from inserted)
select @emp_payrate=(select emp_payrate from inserted)
select @total_sales=(select total_sales from inserted)
select @termination=(select termination from inserted)
select @termin_date=(select termin_date from inserted)

ELSE IF EXISTS
(SELECT 1 from employee
WHERE @emp_first=employee.emp_first
AND @emp_last=employee.emp_last)
Begin
Print'Employee '+ @emp_first + ' ' + @emp_last
+ ' already exists'
Print'Insert Fails'
ROLLBACK TRANSACTION
END

The problem i have is that no matter the First name and Last name exist or not, it will rollback the transaction. I kinda know the reason why, bcuz tigger is being insert first then compare the same insert value. How can I fix this problem?

The second question I have is that on the termination date, is there anyway I can store NULL instead of the system store a default day which is 1900/xx/xx. I have try to use the following.

EXEC sp_addtype nulldate, datetime, 'NULL'
GO

Any comment would be appreciate. Thank you!!

You must handle the case with multiple rows being inserted so you can not use variables like in your example.

You shouldn't use a trigger for this. Use a unique constraint.

How can I have a unique constraint for both First & Last Name together, but not unique constraint on individual attribute?

BTW, the code you have does work... it show me an error message about the "where" cause...????

What error do you get? I assume you corrected the misspelling of inserted.

swampBoogie, thank you for all your reply. I have another question about trigger on deleteion.

create trigger trg_del_emp
on employee
for delete as

IF EXISTS
(SELECT 1 FROM employee
WHERE employee.termination='NO')
Begin
Print'Employee are not yet terminate due to he/she is active employee'
Print'Delete Failed'
ROLLBACK TRANSACTION
END

why this doesn't work even thou if an employee is has a termination of "YES"? How could i fix it?

Shouldn't you check for this condition in the deleted table only?

I am trying to write a trigger on insert. However, on the phone field I want to limited the area code that is allow to enter. The code below doesn't seem working. Any suggestion?

create trigger trg_add_cust
on customer
for insert as

declare @cust_last varchar(20),
@cust_first varchar(20),
@cust_phone varchar(15),
@cust_add varchar(30),
@cust_city varchar(20),
@cust_state varchar(2),
@cust_zip numeric(5,0),

select @cust_last=(select cust_last from inserted)
select @cust_last=(select cust_last from inserted)
select @cust_phone=(select cust_phone from inserted)
select @cust_add=(select cust_add from inserted)
select @cust_city=(select cust_city from inserted)
select @cust_state=(select cust_state from inserted)
select @cust_zip=(select cust_zip from inserted)

IF NOT EXISTS
(SELECT 1 FROM CUSTOMER
WHERE @cust_phone = '410%'
OR @cust_phone ='301%')
Begin
Print 'Area code enter must be either 410 or 301'
Print 'Please re-enter'
ROLLBACK TRANSACTION
END

I think I got it, does this code look ok?

IF NOT EXISTS
(SELECT 1 FROM CUSTOMER
WHERE @cust_phone like '[410]%'
OR @cust_phone like '[301]%')

You shouldn't use a trigger for that. Use a check constraint.

I could be wrong but shouldn't this read:


ELSE IF EXISTS
(SELECT 1 from employee
WHERE employee.emp_first = @emp_first
AND employee.emp_last = @emp_last)
Begin
Print'Employee '+ @emp_first + ' ' + @emp_last
+ ' already exists'
Print'Insert Fails'
ROLLBACK TRANSACTION
END

Using the comparison of employee.emp_first = @emp_first....
Instead of the assignment of @emp_first = employee.emp_first?

Nope. It is not considered to be an assignment in this context.

Whether you use



or



does not matter.

SQL server will have compatibilty issues with implementing a boolean type though.

Once again I thank you for all the reply. Especially one from “swampBoogie.” I do learn a lot by asking. As of the original post, I decide to use unique constraint when I create the table. I make so much sense, even thou it take away my cheesy respond message from trigger.

The second or third question I have is about DATEFIFF:

create table time_detail(
emp_id numeric(5,0) not null foreign key references employee(emp_id),
time_in smalldatetime not null default getdate(),
time_out smalldatetime not null default '')


select 'Total Hours' = datediff(minute, 4/26/2004 9:00 4/26/2004 17:15) from time_detail

Is there are anyway I can compare two day and time to have a result that tell me how many hours with minute different. For the example above, it is 8 hours and 15 mins different. In mySQL is there a way to do something like this?

In other word, I want the system to calculate and list all emp_id, time_in, time_out, and an extra column that tell me how many hours with minutes an employee work? Thank you in advance.