Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #944 > unrolled thread

Trigger help

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Trigger help Johnnyb <john.lutheran@gmail.com> - 2012-03-29 05:47 -0700
    Re: Trigger help Johnnyb <john.lutheran@gmail.com> - 2012-03-30 07:35 -0700
    Re: Trigger help bradbury9 <ray.bradbury9@gmail.com> - 2012-03-29 15:46 -0700
    Re: Trigger help Erland Sommarskog <esquel@sommarskog.se> - 2012-04-02 23:45 +0200
      Re: Trigger help Johnnyb <john.lutheran@gmail.com> - 2012-04-02 15:04 -0700
        Re: Trigger help Erland Sommarskog <esquel@sommarskog.se> - 2012-04-03 07:33 +0000
      Re: Trigger help Gene Wirchenko <genew@ocis.net> - 2012-04-02 20:52 -0700
        Re: Trigger help Erland Sommarskog <esquel@sommarskog.se> - 2012-04-03 07:26 +0000
          Re: Trigger help Johnnyb <john.lutheran@gmail.com> - 2012-04-03 05:03 -0700
          Re: Trigger help Gene Wirchenko <genew@ocis.net> - 2012-04-03 08:17 -0700
      Re: Trigger help Johnnyb <john.lutheran@gmail.com> - 2012-04-04 07:05 -0700
        Re: Trigger help Erland Sommarskog <esquel@sommarskog.se> - 2012-04-04 23:45 +0200
        Re: Trigger help Gene Wirchenko <genew@ocis.net> - 2012-04-04 14:58 -0700
          Re: Trigger help Erland Sommarskog <esquel@sommarskog.se> - 2012-04-05 09:08 +0200
            Re: Trigger help Johnnyb <john.lutheran@gmail.com> - 2012-04-05 06:06 -0700
              Re: Trigger help Gene Wirchenko <genew@ocis.net> - 2012-04-05 08:24 -0700
              Re: Trigger help Erland Sommarskog <esquel@sommarskog.se> - 2012-04-05 23:51 +0200
            Re: Trigger help Gene Wirchenko <genew@ocis.net> - 2012-04-05 08:19 -0700

#944 — Trigger help

I need to create a trigger that will fire when particular text shows up in a particular column. Then I need to copy that row to another table. Not sure how to do this. Any help would be greatly appreciated.

[toc] | [next] | [standalone]

#945

On Thursday, March 29, 2012 4:46:57 PM UTC-6, bradbury9 wrote:
> El jueves 29 de marzo de 2012 14:47:26 UTC+2, Johnnyb  escribió:
> > I need to create a trigger that will fire when particular text shows up in a particular column. Then I need to copy that row to another table. Not sure how to do this. Any help would be greatly appreciated.
> 
> You cannot make the trigger fire when a particular data is set, it will fire always. The way to do it the way you like it is make a trigger with an IF inside and checking if that column has the text desired.
> 
> Create trigger trigger_name on table_name 
> [for|after] update
> as 
> begin
> 
> end

That is kind of what I meant, I just didn't have the terminology correct. Thanks!

[toc] | [prev] | [next] | [standalone]

#946

El jueves 29 de marzo de 2012 14:47:26 UTC+2, Johnnyb  escribió:
> I need to create a trigger that will fire when particular text shows up in a particular column. Then I need to copy that row to another table. Not sure how to do this. Any help would be greatly appreciated.

You cannot make the trigger fire when a particular data is set, it will fire always. The way to do it the way you like it is make a trigger with an IF inside and checking if that column has the text desired.

Create trigger trigger_name on table_name 
[for|after] update
as 
begin

end

[toc] | [prev] | [next] | [standalone]

#947

Johnnyb (john.lutheran@gmail.com) writes:
> I need to create a trigger that will fire when particular text shows up
> in a particular column. Then I need to copy that row to another table.
> Not sure how to do this. Any help would be greatly appreciated. 

CREATE TRIGGER tri ON tbl AFTER INSERT, UPDATE AS
IF EXISTS (SELECT *
           FROM  inserted
           WHERE  col LIKE '%particular text%')
BEGIN
   -- Do something here.
END


inserted holds the inserted rows, or in case of an UDPATE statement, the
updated rows after the UPDATE. 

Don't forget to consider what should happen if the text appears both 
before and after the update.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#948

OK, I am a total novice, it is the 'Do Something Here' part I am stuck on. I want to copy just the row into s different database on a different server.

On Monday, April 2, 2012 3:45:21 PM UTC-6, Erland Sommarskog wrote:
> Johnnyb (john.lutheran@gmail.com) writes:
> > I need to create a trigger that will fire when particular text shows up
> > in a particular column. Then I need to copy that row to another table.
> > Not sure how to do this. Any help would be greatly appreciated. 
> 
> CREATE TRIGGER tri ON tbl AFTER INSERT, UPDATE AS
> IF EXISTS (SELECT *
>            FROM  inserted
>            WHERE  col LIKE '%particular text%')
> BEGIN
>    -- Do something here.
> END
> 
> 
> inserted holds the inserted rows, or in case of an UDPATE statement, the
> updated rows after the UPDATE. 
> 
> Don't forget to consider what should happen if the text appears both 
> before and after the update.
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#951

Johnnyb (john.lutheran@gmail.com) writes:
> OK, I am a total novice, it is the 'Do Something Here' part I am stuck
> on. I want to copy just the row into s different database on a different
> server. 
 
INSERT SERVER.dataase.dbo.tbl(....)
   SELECT ....  FROM inserted

Where you previously have defined the linked server with sp_addlinkedserver.

However, there are two different complications here.

One is what you exactly mean with "copy". Does that other table have the 
same schema? What if there already is a row with the same key value? Or is 
this not an issue?

The other is the fact you the table is on a different server. It may all go 
smoothly. Or it may give you one hell of a headache to get working. It may 
work for one user, it may fail for another.

If you give the full plot of why you want to copy this row and in what sort
of environment you are working in, we might be able to give your more exact
advice.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#949

On Mon, 02 Apr 2012 23:45:21 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Johnnyb (john.lutheran@gmail.com) writes:
>> I need to create a trigger that will fire when particular text shows up
>> in a particular column. Then I need to copy that row to another table.
>> Not sure how to do this. Any help would be greatly appreciated. 
>
>CREATE TRIGGER tri ON tbl AFTER INSERT, UPDATE AS
>IF EXISTS (SELECT *
>           FROM  inserted
>           WHERE  col LIKE '%particular text%')
>BEGIN
>   -- Do something here.
>END
>
>
>inserted holds the inserted rows, or in case of an UDPATE statement, the
>updated rows after the UPDATE. 
>
>Don't forget to consider what should happen if the text appears both 
>before and after the update.

     Why would this be relevant since the trigger is for after the
insertion?  Assuming the above is correct, I think that I am missing a
nuance.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#950

Gene Wirchenko (genew@ocis.net) writes:
>      Why would this be relevant since the trigger is for after the
> insertion?  Assuming the above is correct, I think that I am missing a
> nuance.
 
CREATE TRIGGER tri ON tbl AFTER INSERT, UPDATE AS

Notice the word UPDATE?

True, I don't know whether Johnnyb wants to handle updates. He only 
says "when particular text shows up in a particular column". Really what
mean I don't know.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#952

OK, so there re no UPDATES, only insertions. This is a logfile from an application. For every even, there is a row inserted. When a row is insert3ed with a particular word in a particular column, I want to copy that row to another server.


On Tuesday, April 3, 2012 1:26:12 AM UTC-6, Erland Sommarskog wrote:
> Gene Wirchenko (genew@ocis.net) writes:
> >      Why would this be relevant since the trigger is for after the
> > insertion?  Assuming the above is correct, I think that I am missing a
> > nuance.
>  
> CREATE TRIGGER tri ON tbl AFTER INSERT, UPDATE AS
> 
> Notice the word UPDATE?
> 
> True, I don't know whether Johnnyb wants to handle updates. He only 
> says "when particular text shows up in a particular column". Really what
> mean I don't know.
> 
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx



On Tuesday, April 3, 2012 1:26:12 AM UTC-6, Erland Sommarskog wrote:
> Gene Wirchenko (genew@ocis.net) writes:
> >      Why would this be relevant since the trigger is for after the
> > insertion?  Assuming the above is correct, I think that I am missing a
> > nuance.
>  
> CREATE TRIGGER tri ON tbl AFTER INSERT, UPDATE AS
> 
> Notice the word UPDATE?
> 
> True, I don't know whether Johnnyb wants to handle updates. He only 
> says "when particular text shows up in a particular column". Really what
> mean I don't know.
> 
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx



On Tuesday, April 3, 2012 1:26:12 AM UTC-6, Erland Sommarskog wrote:
> Gene Wirchenko (genew@ocis.net) writes:
> >      Why would this be relevant since the trigger is for after the
> > insertion?  Assuming the above is correct, I think that I am missing a
> > nuance.
>  
> CREATE TRIGGER tri ON tbl AFTER INSERT, UPDATE AS
> 
> Notice the word UPDATE?
> 
> True, I don't know whether Johnnyb wants to handle updates. He only 
> says "when particular text shows up in a particular column". Really what
> mean I don't know.
> 
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#953

On Tue, 3 Apr 2012 07:26:12 +0000 (UTC), Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      Why would this be relevant since the trigger is for after the
>> insertion?  Assuming the above is correct, I think that I am missing a
>> nuance.
> 
>CREATE TRIGGER tri ON tbl AFTER INSERT, UPDATE AS
>
>Notice the word UPDATE?

     Yes.  Why does an after-update trigger make a difference about if
the condition exists before?

[snip]

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#954

OK, here's more information:

The table I'm adding this trigger to is replicated from another server. When I add the trigger:

USE [NDII_MAIN]
GO
/****** Object:  Trigger [dbo].[880IOT]    Script Date: 04/04/2012 07:54:08 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER 
    [dbo].[880IOT]
ON 
    [dbo].[logfile0]
FOR INSERT, UPDATE 
AS
  BEGIN
    SET NOCOUNT ON
      INSERT INTO [DB02TSSNT].[Test].[dbo].[880io]
      (
    eventcode,
    cardname,
	eventnumber,
	eventdatetime,
	pointname
      )
    SELECT
    eventcode,
    cardname,
	eventnumber,
	eventdatetime,
	pointname
    FROM INSERTED
    WHERE POINTNAME LIKE 'XXXXX' OR POINTNAME LIKE 'YYYYY'
END

It stops the replication. What am I doing wrong?



On Monday, April 2, 2012 3:45:21 PM UTC-6, Erland Sommarskog wrote:
> Johnnyb (john.lutheran@gmail.com) writes:
> > I need to create a trigger that will fire when particular text shows up
> > in a particular column. Then I need to copy that row to another table.
> > Not sure how to do this. Any help would be greatly appreciated. 
> 
> CREATE TRIGGER tri ON tbl AFTER INSERT, UPDATE AS
> IF EXISTS (SELECT *
>            FROM  inserted
>            WHERE  col LIKE '%particular text%')
> BEGIN
>    -- Do something here.
> END
> 
> 
> inserted holds the inserted rows, or in case of an UDPATE statement, the
> updated rows after the UPDATE. 
> 
> Don't forget to consider what should happen if the text appears both 
> before and after the update.
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#955

Johnnyb (john.lutheran@gmail.com) writes:
> The table I'm adding this trigger to is replicated from another server. 
> When I add the trigger:
>...
> It stops the replication. What am I doing wrong?
 
Didn't I say that it was simple to implement and a headache to get
working?

Did you try running the trigger on its own?

What happens if use a table on the same database instead?

Have you checked the replication logs for errors? (Sorry, I don't do
replication very often, so I don't know on the top of my head where to 
look.)

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#956

On Wed, 4 Apr 2012 07:05:17 -0700 (PDT), Johnnyb
<john.lutheran@gmail.com> wrote:

[snip]

>It stops the replication. What am I doing wrong?

     I do not know quite enough to be sure, but I believe that your
trigger's insert is replacing the insert/update.  I believe you need
the magic word "AFTER":

CREATE TRIGGER 
    [dbo].[880IOT]
ON 
    [dbo].[logfile0]
after INSERT, UPDATE 
AS
...

[snip]

Sioncerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#957

Gene Wirchenko (genew@ocis.net) writes:
>      I do not know quite enough to be sure, but I believe that your
> trigger's insert is replacing the insert/update.  I believe you need
> the magic word "AFTER":
 
FOR and AFTER are equivalent.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#958

Even with 'AFTER' I get this in my replication monitor:
Command attempted:
if @@trancount > 0 rollback tran
(Transaction sequence number: 0x00000771000FB566000300000000, Command ID: 1)

Error messages:
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "DB02TSSNT" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "DB02TSSNT" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
Get help: http://help/7391


On Thursday, April 5, 2012 1:08:12 AM UTC-6, Erland Sommarskog wrote:
> Gene Wirchenko (genew@ocis.net) writes:
> >      I do not know quite enough to be sure, but I believe that your
> > trigger's insert is replacing the insert/update.  I believe you need
> > the magic word "AFTER":
>  
> FOR and AFTER are equivalent.
> 
> 
> -- 
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
> 
> Links for SQL Server Books Online:
> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#960

On Thu, 5 Apr 2012 06:06:04 -0700 (PDT), Johnnyb
<john.lutheran@gmail.com> wrote:

>Even with 'AFTER' I get this in my replication monitor:
>Command attempted:
>if @@trancount > 0 rollback tran
>(Transaction sequence number: 0x00000771000FB566000300000000, Command ID: 1)
>
>Error messages:
>The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "DB02TSSNT" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
>Get help: http://help/7391
>The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "DB02TSSNT" was unable to begin a distributed transaction. (Source: MSSQLServer, Error number: 7391)
>Get help: http://help/7391

     You really ought to have posted this with your previous post.  I
would not have replied since it is obviously outside of what I know.
Erland (and others knowledgeable) would have had more to work with.

     This link is *very* useful:
          http://www.catb.org/~esr/faqs/smart-questions.html
          How To Ask Questions The Smart Way

[snip]

Sincerely,

Gene Wirchenko

[toc] | [prev] | [next] | [standalone]

#961

Johnnyb (john.lutheran@gmail.com) writes:
> Error messages:
> The operation could not be performed because OLE DB provider "SQLNCLI10"
> for linked server "DB02TSSNT" was unable to begin a distributed
> transaction. (Source: MSSQLServer, Error number: 7391) 
> Get help: http://help/7391
> The operation could not be performed because OLE DB provider "SQLNCLI10"
> for linked server "DB02TSSNT" was unable to begin a distributed
> transaction. (Source: MSSQLServer, Error number: 7391) 
> Get help: http://help/7391
 
That may be difficult to overcome. I'm not always successful myself.

Are these two servers in the same domain? Are they in a domain at all?

Since you already have replication in place, maybe it would be better
to set up the database as a subscriber, and then have a replication
filter, so that only the relevant rows are inserted?

As I said, linked servers in triggers can be a headache, and I would
certainly like to try alternative solutions.

-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[toc] | [prev] | [next] | [standalone]

#959

On Thu, 05 Apr 2012 09:08:12 +0200, Erland Sommarskog
<esquel@sommarskog.se> wrote:

>Gene Wirchenko (genew@ocis.net) writes:
>>      I do not know quite enough to be sure, but I believe that your
>> trigger's insert is replacing the insert/update.  I believe you need
>> the magic word "AFTER":
> 
>FOR and AFTER are equivalent.

     Hmm, I must have gotten something a bit confused.  FOR with
INSTEAD OF?  Oh, well.  When I get back to the SQL Server side of my
app-to-be, I will have to pay more attention to triggers.  Thank you
for the correction.

Sincerely,

Gene Wirchenko

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web