473,698 Members | 1,947 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

How to fire a trigger without changing table data

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
5 1665
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

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

"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
>>The trigger was firing but not changing data.
Thats what your question says

Madhivanan

Mar 28 '06 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
3443
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')
4
6295
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...
2
2649
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
18
5981
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) " ,...
3
5756
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?
2
10005
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
2
4970
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();
19
4753
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)
2
2989
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
0
8672
marktang
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...
0
9155
Oralloy
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...
0
9018
jinu1996
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...
1
8890
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,...
0
7711
agi2029
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...
1
6517
isladogs
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...
0
4360
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...
2
2322
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1997
bsmnconsultancy
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.