njgroup
asked on
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
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
>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?
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?
ASKER
qasim_md, is that oracle or ms sql?
I dont see before as keyword
I got error on it
I dont see before as keyword
I got error on it
that was MySQL syntax, which won't work on MS SQL server
ASKER
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?
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?
ASKER
IF select count(*) from myrable where EnProductType like %selected_EnProductType % > 0
Then DONT INSERT THE ROW
ELSE INSERT THE ROW
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.
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.
ASKER
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
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(in serted.xyz ))
if @nRow>=1
Begin
Roll back Tran
end
else
update Table1 Set XYZ =lower(XYZ)
end
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(in
if @nRow>=1
Begin
Roll back Tran
end
else
update Table1 Set XYZ =lower(XYZ)
end
ASKER
I did one, but its not working for instead of insert:
but its not working:
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
but the problem I dont want to insert then roll back!
I wanna do before insert (instead of insert)
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
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 ...
which is what I suggested in my first comment already :)
...otherwise, you needed a INSTEAD OF trigger, that could silently "drop" the INSERT ...
ASKER
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.
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
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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);