?
Solved

Write a TRIGGER based on the attribute / field value being insert

Posted on 2013-05-16
3
Medium Priority
?
286 Views
Last Modified: 2013-05-20
I would like to write a MS-SQL Server TRIGGER based on a particular Field value and not sure if it is possible or not.

For example if (and Only if ) someone or any application has insert a record with a certain field (e.g. Field A) value is "U", then this event will fire this trigger, is it possible on MS-SQL ?

My company are using an MS-SQL Server 2008 Express Edition.

Thank you so much for your help in advance.
0
Comment
Question by:patricktam
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 25

Expert Comment

by:Lee Savidge
ID: 39170692
CREATE TRIGGER triggername
ON tablename
FOR INSERT
AS
BEGIN
  if exists (select myfield from inserted where myfield = 'U')
  begin
    -- do stuff
  end
END
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 39171279
create instead of trigger and check if the inserted value is as expected then move ahead to insert record in table.


CREATE TRIGGER triggername
ON tablename
INSTEAD OF INSERT
AS
BEGIN
  if exists (select myfield from inserted where myfield = 'U')
  begin
    -- do stuff
  end
END
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 1500 total points
ID: 39172133
I don't think you can base whether the trigger fires or not on a column value: the trigger will always fire.

But you could have the trigger exit if the specific column you're concerned with was not modified.


CREATE TRIGGER triggername
ON dbo.tablename
AFTER INSERT
AS
SET NOCOUNT ON
IF NOT EXISTS(SELECT 1 FROM inserted WHERE certain_column = 'certain_value')
    --no row has the column value I'm concerned about, so exit the trigger immediately
    RETURN
--at least one row has the "trigger" value, do related processing here
--...processing code here
GO
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question