How to fire a trigger without changing table data

John Smith

I have tables that I want to fire either an update or insert trigger on.

I could write a script containing a long list of inserts but I'm looking for
something simpler. Would isql work? Any special conditions to get it to
work?

I've tried tricks like 'update x set col = col' or 'update x set col = col +
'' '

All the alternatives seem to have problems. Any ideas?

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

Mar 28 '06 #1

Subscribe Reply

1665

Tom Moreau

Try:

update MyTable
set
Col1 = 'x'
where
1 = 2

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
..
"John Smith" <no****@nowhere .com> wrote in message
news:11******** ******@sp6iad.s uperfeed.net...
I have tables that I want to fire either an update or insert trigger on.

I could write a script containing a long list of inserts but I'm looking for
something simpler. Would isql work? Any special conditions to get it to
work?

I've tried tricks like 'update x set col = col' or 'update x set col = col +
'' '

All the alternatives seem to have problems. Any ideas?

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

Mar 28 '06 #2

John Smith

"Tom Moreau" <to*@dont.spam. me.cips.ca> wrote in message
news:SL******** **********@news 20.bellglobal.c om...

Try:

update MyTable
set
Col1 = 'x'
where
1 = 2

Thanks, but it doesn't work.

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

Mar 28 '06 #3

Dan Guzman

> Thanks, but it doesn't work.

The script Tom posted works for me: Please expand on what you mean by 'it
doesn't work'.

CREATE TABLE MyTable(Col1 int)
GO

CREATE TRIGGER TR_MyTable
ON MyTable FOR INSERT, UPDATE AS
PRINT 'Trigger fired'
GO

UPDATE MyTable
SET Col1 = 'x'
WHERE 1 = 2
GO

DROP TABLE MyTable
GO

--
Hope this helps.

Dan Guzman
SQL Server MVP

"John Smith" <no****@nowhere .com> wrote in message
news:11******** ******@sp6iad.s uperfeed.net...

"Tom Moreau" <to*@dont.spam. me.cips.ca> wrote in message
news:SL******** **********@news 20.bellglobal.c om...
Try:

update MyTable
set
Col1 = 'x'
where
1 = 2

Thanks, but it doesn't work.
----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet
News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+
Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption
=----

Mar 28 '06 #4

John Smith

"Dan Guzman" <gu******@nospa m-online.sbcgloba l.net> wrote in message
news:_s******** *********@newss vr27.news.prodi gy.net...

Thanks, but it doesn't work.

The script Tom posted works for me: Please expand on what you mean by 'it
doesn't work'.

Thanks for the help. The problem was due to NULL values in some columns.

The trigger was firing but not changing data.

----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==----
http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups
----= East and West-Coast Server Farms - Total Privacy via Encryption =----

Mar 28 '06 #5

Madhivanan

>>The trigger was firing but not changing data.
Thats what your question says

Madhivanan

Mar 28 '06 #6

Similar topics

3443

Trigger failing after table structure change

by: Dave C. | last post by:

Hello, I have created the following trigger: CREATE TRIGGER tr_UDFTest ON UserDefinedFields FOR INSERT, UPDATE AS DECLARE @foobar varchar(100) SELECT @foobar= foobar FROM inserted IF ( @foobar = 'foobar') INSERT INTO LogTable (LogText) values ('Found foobar')

Microsoft SQL Server

6295

Trigger Problems

by: Steve Bishop | last post by:

I have an Insert, Update and delete trigger on a table that works fine. It records the changes into another table called tblTracking. tblTracking records the changes so they can be uploaded to another server off site. The problem is that it fires each time my DTS package fires (every 15 minutes). The DTS package imports data from an ODBC database then compares what is in the source/destination table with a Insert Into, select not in (for...

Microsoft SQL Server

2649

Precedence of a TRIGGER vs. a CHECK on a column

by: ezra epstein | last post by:

Hi, I've got a table: <code language="SQL"> CREATE TABLE "common"."dynamic_enum" ( "pk_id" integer DEFAULT nextval('"common"."pw_seq"') , "enum_type" common.non_empty_name

PostgreSQL Database

5981

Help with like predicate in update trigger

by: Bill Smith | last post by:

The initial row is inserted with the colPartNum column containing a valid LIKE pattern, such as (without the single quotes) 'AB%DE'. I want to update the column value with the results of a query against a different table (that uses the LIKE predicate) but cannot get around the SQL0132 error . I have tried the hex notation after the LIKE such as (without the quotes)... " where colNewPartNum like ( X'27' || nnn.colPartNum || X'27) " ,...

DB2 Database

5756

Triggers - can I have it fire only if certain columns actually change?

by: dataguy | last post by:

I can't find it anywhere in the manual, but I have a developer that wants to know if we can code a trigger to capture the data that has changed in a table only if certain columns have changed. It looks like I can do it with the when clause and check the old vs new, but that would get very ugly with a large table. Has anyone done something like this?

DB2 Database

10005

update statement not to fire trigger

by: Dima Gofman | last post by:

I have a trigger on UPDATE on a table. I'm running some maintenance UPDATE and DELETE queries which I want the trigger to ignore but at the same time I want other UPDATE queries that other users might be running to keep triggering the trigger. Is there a SET statement perhaps that I could set before my query? Or a clause in the UPDATE statement? This is on MSSQL 2000 server, on Win2k3

Microsoft SQL Server

4970

same trigger problem

by: mob1012 via DBMonster.com | last post by:

Hi All, I wrote last week about a trigger problem I was having. I want a trigger to produce a unique id to be used as a primary key for my table. I used the advice I received, but the trigger is still not working correctly. Here is my code: create trigger emp_update_id BEFORE update on emp_update REFERENCING NEW AS N for each row SET unique_id = Generate_unique();

DB2 Database

4753

How to fire an event

by: Daniela Roman | last post by:

Hello, I try to fire an event under a button click event and maybe anybody can give a clue please. I have let's say a WEB grid with PageIndexChanged event: private void DataGrid1_PageIndexChanged(object source, System.Web.UI.WebControls.DataGridPageChangedEventArgs e)

C# / C Sharp

2989

Accessing which data caused a trigger to fire

by: jcandersen | last post by:

Hi. I need to make a trigger which fires when after an UPDATE is done on table A in database X. The trigger must perform a statement which copies some of the data from the update to table B in database Y on the same server. My question is: Can I access the data which caused the trigger to fire or just get an indication of which entry in table A had been updated? Best Regards

Microsoft SQL Server

8672

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

9155

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

9018

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

8890

The easy way to turn off automatic updates for Windows 10/11

by: Hystou | last post by:

Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...

Windows Server

7711

AI Job Threat for Devs

by: agi2029 | last post by:

Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...

Career Advice

6517

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

4360

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

2322

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP

1997

Comprehensive Guide to Website Development in Toronto: Expert Insights from BSMN Consultancy

by: bsmnconsultancy | last post by:

In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

General