Solved

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

Posted on 2013-05-16
3
275 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
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:
ScottPletcher earned 500 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL Server Degrading on Write 13 63
How to query LOCK_ESCALATION 4 40
How to order Highest and lowest value rows alternatively in SQL Server ? 4 44
sql query help 2 43
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

919 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now