Solved

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

Posted on 2013-05-16
3
281 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:
Scott Pletcher 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

791 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