Path: csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!nx02.iad01.newshosting.com!newshosting.com!69.16.185.11.MISMATCH!npeer01.iad.highwinds-media.com!news.highwinds-media.com!feed-me.highwinds-media.com!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
From: Johnnyb <john.lutheran@gmail.com>
Newsgroups: comp.databases.ms-sqlserver
Subject: Re: Trigger help
Date: Wed, 4 Apr 2012 07:05:17 -0700 (PDT)
Organization: http://groups.google.com
Lines: 70
Message-ID: <17890250.1123.1333548317738.JavaMail.geo-discussion-forums@pbcoy7>
References: <11931752.1474.1333025246004.JavaMail.geo-discussion-forums@pbjv6> <XnsA029F1A89853BYazorman@127.0.0.1>
NNTP-Posting-Host: 134.253.26.11
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
X-Trace: posting.google.com 1333548318 10740 127.0.0.1 (4 Apr 2012 14:05:18 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Wed, 4 Apr 2012 14:05:18 +0000 (UTC)
In-Reply-To: <XnsA029F1A89853BYazorman@127.0.0.1>
Complaints-To: groups-abuse@google.com
Injection-Info: glegroupsg2000goo.googlegroups.com; posting-host=134.253.26.11; posting-account=kYW--QoAAABRuO6dTOz2QITtLsB-PRVZ
User-Agent: G2/1.0
X-Received-Bytes: 2870
Xref: csiph.com comp.databases.ms-sqlserver: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