Solved

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

Posted on 2013-05-16
3
273 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
Comment Utility
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
Comment Utility
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
Comment Utility
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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SQL Server; storing data in offline mode. 10 57
SQL Server DeDupe Table 3 29
Sql query 12 65
SQL Agent Timeout 5 38
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…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
This video discusses moving either the default database or any database to a new volume.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

772 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

11 Experts available now in Live!

Get 1:1 Help Now