Trigger - On insert ~ if [column] value = XX, change it to YY, then insert

[fatjoez]

Hi there.

I'm trying to create a Trigger for on insert, which will be triggered upon any attempted insert into a database table item. Basically want it to say, if the attempted insert value for column A is XXX, then change it to YYY, and proceed with the insert.

Not sure how to do this though. Only noob @ triggers. I managed to do a trigger which AFTER an insert, checks column [info] in item table for value 8 & then sets it to 0.

can anyone tell me how to modify my existing trigger to achieve this?

================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Name
-- Create date: 
-- Description:    
-- =============================================
CREATE TRIGGER dbo.gmItem 
   ON  dbo.item 
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    update item set [info] = '0' where [info] = '8';
    -- Insert statements for trigger here
 
END
GO
 

[Senthil]

--declare the variable @info
select @info =info from inserted.
if @info=8
begin
--update statement
end


Cheers,
Sharmila
-------------------------

Hi there.

I'm trying to create a Trigger for on insert, which will be triggered upon any attempted insert into a database table item. Basically want it to say, if the attempted insert value for column A is XXX, then change it to YYY, and proceed with the insert.

Not sure how to do this though. Only noob @ triggers. I managed to do a trigger which AFTER an insert, checks column [info] in item table for value 8 & then sets it to 0.

can anyone tell me how to modify my existing trigger to achieve this?

================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Name
-- Create date: 
-- Description:    
-- =============================================
CREATE TRIGGER dbo.gmItem 
   ON  dbo.item 
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    update item set [info] = '0' where [info] = '8';
    -- Insert statements for trigger here
 
END
GO
 

[Shaily]

Hi there.

I'm trying to create a Trigger for on insert, which will be triggered upon any attempted insert into a database table item. Basically want it to say, if the attempted insert value for column A is XXX, then change it to YYY, and proceed with the insert.

Not sure how to do this though. Only noob @ triggers. I managed to do a trigger which AFTER an insert, checks column [info] in item table for value 8 & then sets it to 0.

can anyone tell me how to modify my existing trigger to achieve this?

================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Name
-- Create date: 
-- Description:    
-- =============================================
CREATE TRIGGER dbo.gmItem 
   ON  dbo.item 
   AFTER INSERT
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
    update item set [info] = '0' where [info] = '8';
    -- Insert statements for trigger here
 
END
GO
 

What i understand is that u want to insert a changed valur for a perticular column.
If that is so, try to use "Insead Of Insert".


declare @info
selsct @info = info from inserted
if @info = 8
Begin
-----rewrite the insert statement with the value of info change
end

else
begin

---- rewrite the insert statement with the original data.

end





Hope this will heip. For further clarification on "Instead Of Insert" search for "Instead Of Insert" in BOL.


Regareds
Shail