how to make before insert trigger in sql server 2005?

I hope this helps:::

The Create Before Insert Trigger in SQL fires the trigger before we insert the value into the table.

Understand with Example

The given Tutorial describe you a code on 'Create Before Insert Trigger in SQL'. To grasp this example, we create a table 'Stu_Table'. The createtable is used to create a table 'Stu_Table' with field attribute and data type respectively.

Create Table Stu_Table

Create Table Stu_Table
(Stu_Id int,Stu_Name Varchar(15),Sub1 int,Sub2 int,Sub3 int,
Sub4 int,Sub5 int,total int,per float,status varchar(15));

Create Trigger Stu_Insert

Now, we create a Trigger 'Stu_Insert' on table stu_table. The Before Insert ON  trigger is fired first before adding a records or rows to the table 'Stu_Table'.  

delimiter $$

CREATE TRIGGER stu_insert

Before Insert ON stu_table FOR EACH ROW
BEGIN

      set new.total = new.sub1 + new.sub2 +new.sub3 +
                  new.sub4 +new.sub5;
      set new.per = new.total/5;

      if new.per<33 then
            set new.status="fail";

      elseif new.per>=33 and new.per<45 then
            set new.status="3rd Div";

      elseif new.per>=45 and new.per<60 then
            set new.status="2nd  Div";

      else
            set new.status="1st Div";
      end if;
END$$
delimiter ;

Insert Data Into Stu_Table

insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
                   values (1, 'AAA', 6, 6, 6, 6, 6);

insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
                   values (2, 'BBB', 33, 33, 33, 33, 40);

insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
                   values (2, 'CCC', 45, 45, 45, 45, 50);

insert into stu_table( Stu_Id, Stu_Name, sub1, sub2, sub3, sub4, sub5)
                   values (2, 'DDD', 67, 67, 67, 67, 67);

>if there is match, the record must not be inserted, otherwise the record can be inserted

you could implement that part using a unique constraint, refusing the insert.
otherwise, you needed a INSTEAD OF trigger, that could silently "drop" the INSERT ...

in regards to the trigger syntax itself, have you already tried something?

qasim_md, is that oracle or ms sql?

I dont see before as keyword

I got error on it

that was MySQL syntax, which won't work on MS SQL server

so any expert can help me in MS SQL 2005

I dont want mysql or oracle or any other,

I need it in ms sql 2005,

I have a column "EnProductType" in database, I just want to compare the inserted value if it is similar or like (%) all values of EnProductType of the table then the row must not be inserted.

I mean

IF select count(*) from myrable where EnProductType like %selected_EnProductType %
Then DONT INSERT THE ROW
ELSE  INSERT THE ROW


how to do that in a trigger in sql server 2005?

IF select count(*) from myrable where EnProductType like %selected_EnProductType % > 0
Then DONT INSERT THE ROW
ELSE  INSERT THE ROW

I repeat: what about using a unique constraint on the table?
if will raise an error to any application trying to run a INSERT that would result in a duplicate, you don't need a trigger for that.
if the collation of the column is not case senstive, you don't actually need to convert to lowercase to compare (and the unique constraint would not need either)

in other words: are you 200% sure you need a trigger?
a trigger is slower than such a constraint.

the unique constraint is not enough, because I want to use like % string....

for example if I got this value in EnProductType column: "IP Camera"
I want it if someone try to insert record to "EnProductType" with value "ip camera sony" to decline that insert so the trigger must not insert insert this record in the table.

yes I am sure I need it in a trigger

MS SQL Server Create Dynamic Table Named Inserted while Insert and Deleted while Delete and for Update both Table will be reated

You can reference these Table in Trigger ti fire

Create Trigger Tr1
on Table1 for Insert
As
Declare @nRow int
Set @nRow=(Select count(*) from table1 inner join Inserted on lower(Table1.xyz)=lower(inserted.xyz))
if @nRow>=1
Begin
 Roll back Tran
end
else
 update Table1 Set XYZ =lower(XYZ)
end

I did one, but its not working for instead of insert:

but its not working:

 

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER TRIGGER [tgr_ProductType_Instead_Of_Insert] ON [dbo].[ProductType]
INSTEAD OF INSERT
AS

	DECLARE @enProductType varchar(50) 
	DECLARE @arProductType nvarchar(50) 
	DECLARE @enDescription varchar(MAX) 
	DECLARE @arDescription nvarchar(MAX)

	DECLARE @countRecordMatch    int

	select @enProductType = (SELECT dbo.TRIM(EnProductType) FROM inserted)
	select @arProductType = (SELECT dbo.TRIM(ArProductType) FROM inserted)
	select @enDescription = (SELECT CAST(EnDescription AS NVARCHAR(MAX)) FROM inserted)
	select @arDescription = (SELECT CAST(ArDescription AS NVARCHAR(Max)) FROM inserted)

	select @countRecordMatch = (select count(*) from ProductType where UPPER(dbo.TRIM(EnProductType)) = UPPER(@enProductType))

	IF @countRecordMatch = 0
	BEGIN
		insert into ProductType (EnProductType, ArProductType, EnDescription, ArDescription) values (@enProductType, @arProductType, @enDescription, @arDescription)
	END

Select allOpen in new window

but the problem I dont want to insert then roll back!

I wanna do before insert (instead of insert)

You can use Instead of Insert in Place of for Insert and change the If condition

if @nRow=0
Begin
   Insert into Table1
   Select * from Inserted
end

:)

which is what I suggested in my first comment already :)
...otherwise, you needed a INSTEAD OF trigger, that could silently "drop" the INSERT ...

but there is problem here in:

   Insert into Table1
   Select * from Inserted

because the id of the table is auto increment

here is the error:

Msg 8101, Level 16, State 1, Procedure tgr_ProductType_Instead_Of_Insert, Line 16
An explicit value for the identity column in table 'ProductType' can only be specified when a column list is used and IDENTITY_INSERT is ON.