[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1078
  • Last Modified:

how to make before insert trigger in sql server 2005?

hi,

I want to create new trigger that should be triggered before inserting the record, the trigger is going to convert XYZ field nvarchar string from inserting record to lower chars and check it across to lower XYZ field in table

if there is match, the record must not be inserted, otherwise the record can be inserted
0
njgroup
Asked:
njgroup
  • 7
  • 5
  • 3
  • +1
2 Solutions
 
qasim_mdCommented:
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);
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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?
0
 
njgroupAuthor Commented:
qasim_md, is that oracle or ms sql?

I dont see before as keyword

I got error on it
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
that was MySQL syntax, which won't work on MS SQL server
0
 
njgroupAuthor Commented:
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?
0
 
njgroupAuthor Commented:
IF select count(*) from myrable where EnProductType like %selected_EnProductType % > 0
Then DONT INSERT THE ROW
ELSE  INSERT THE ROW
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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.
0
 
njgroupAuthor Commented:
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
0
 
samijsrCommented:
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
0
 
njgroupAuthor Commented:
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

Open in new window

0
 
samijsrCommented:
for use the Like Key word Modify the Trigger

Create Trigger Tr1
on Table1 for Insert
As
Declare @nRow int
Declare @xyz nvarchar(50)
Set @xyz=(Select xyz from Inserted)
Set @xyz=lowe(@xyz)+'%'
Set @nRow=(Select count(*) from table1 where xyz like @xyz)
if @nRow>=1
Begin
 Roll back Tran
end
else
 update Table1 Set XYZ =lower(XYZ)
end
0
 
njgroupAuthor Commented:
but the problem I dont want to insert then roll back!

I wanna do before insert (instead of insert)
0
 
samijsrCommented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
:)

which is what I suggested in my first comment already :)
...otherwise, you needed a INSTEAD OF trigger, that could silently "drop" the INSERT ...
0
 
njgroupAuthor Commented:
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.



0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you will have to list all the fields without the ID field in both destination table and source table.

so, you list all the columns except ID column:
Insert into Table1 (col2, col3 ... )
   Select col2, col3 ... 
     from Inserted

Open in new window

0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 7
  • 5
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now