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

1668

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

3447

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

6297

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

2653

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

5983

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

5757

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

10006

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

4974

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

4755

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

2990

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

9384

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++

9157

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

9088

Discussion: How does Zigbee compare with other wireless protocols in smart home applications?

by: tracyyun | last post by:

Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...

General

8052

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

6681

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

4762

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

3207

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2602

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

2147

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