Trigger functionality - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Trigger functionality

Author

Topic

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-03-17 : 04:43:54

Hi,

I have created an trigger for cheking duplicate rows when inserting new row. I have "Dept_New" table, Its having 2 rows (deptno is 10 and 20) already. Now i am trying to insert new record as (deptno is 30), In this scenario, trigger raises error message. why? Pls. clarify to me.

Trigger Code
------------
Create Trigger trEmpCkPk On Dept_New
For Insert
As
Begin
Declare @mEno Int, @Temp Int
If exists(Select e.deptno from Dept_New e, inserted i where i.deptno = e.deptno)
Begin
RaisError('Duplicate Entry',16,1)
RollBack Transaction
End
End

":-)IT Knowledge is power :-)"

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-17 : 05:24:07

AFTER is the default, if FOR is the only keyword specified.
Hence this trigger fires after insertion of the record on the table. so definetly it will throw you a error.
Just give select statement before the if clause and try to insert a row from Query analyser. you will know exactly what happens.

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

samsekar
Constraint Violating Yak Guru

437 Posts

Posted - 2003-03-17 : 05:29:37

Try this:

ALTER Trigger trEmpCkPk On Dept_New
instead of Insert
As
Begin
Declare @mEno Int, @Temp Int
select * from dept_new
select * from inserted
If exists(Select e.deptno from Dept_New e, inserted i where i.deptno = e.deptno)
Begin
RaisError('Duplicate Entry',16,1)
RollBack Transaction
End
else
begin
insert into Dept_New select * from inserted
commit transaction
end
End

Sekar
~~~~
Success is not a destination that you ever reach. Success is the quality of your journey.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-03-17 : 06:53:24

Can you just put a unique index on deptno.

You could make the trigger check for duplicates in the table.

If exists (select deptno from Dept_New group by deptno having count(*) > 1)

or maybe better

If exists (select n.deptno from Dept_New n join inserted i on i.deptno = n.deptno group by n.deptno having count(*) > 1)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Bambola
Posting Yak Master

103 Posts

Posted - 2003-03-17 : 06:56:41

Why don't you use unique constraint?

Bambola.

SqlStar
Posting Yak Master

121 Posts

Posted - 2003-03-17 : 07:11:04

Hi Samsekar,

Thanks for your help. I achived that.

":-)IT Knowledge is power :-)"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources