SQL Triggers (Before) [modified]

Amit Kumar G

I want to restrict user for not adding the new record if table already contains active record. (means Active=0) CREATE TRIGGER TR_TB_QuizMaster ON TB_QuizMaster FOR INSERT AS DECLARE @iCount int BEGIN SELECT @iCount=COUNT(*) FROM TB_QuizMaster WHERE Active=0 Print @iCount IF (@iCount>=1) BEGIN Print 'Active record already exists. Only one active record can exisits' Rollback END ELSE print 'Inserted' END But this does not allow me to enter a new record unless i change it to IF (@iCount>=2). This is because it fires after insert.....Is there any way by which we can fire trigger BEFORE actual DML operation.... Please suggest Thanks!! -- modified at 6:49 Friday 21st September, 2007

Pete OHanlon

There is - it's called an instead of trigger and works by defining a trigger on a view which matches the table structure. What happens is you insert into the view which runs the trigger and then depending on your result, inserts into the appropriate table. From memory, it would look something like this:

create trigger MyTableInsertCheck
on MyView
instead of insert
as
begin
select @count = count(1) from mytable m inner join inserted i on m.keyfield = i.keyfield
if @count = 0
begin
  insert name, keyfield into mytable
  select name, keyfield from myview
end
end

Deja View - the feeling that you've seen this post before.

Amit Kumar G

Thanks and if i don't want to create views....Is there any Before trigger in SQL 2005.....

Pete OHanlon

Amit Kumar G wrote:

Is there any Before trigger in SQL 2005.....

No (except as in the example above).

Deja View - the feeling that you've seen this post before.

Amit Kumar G

thanks!! I just read trigger syntax and would really appreciate if you could help me to understand difference between FOR|After clause....