Ok here goes, another odd SQL Question ....as always..
How do I get ...
The value of a paramater passed to say a Trigger
OR The SQL Itself
Like this , say I have a Trigger set on delete,
Now this trigger will add a row to another table (a history table) but
it need to be passed a paramater, throught the sql itself as it cannot
be any different than a normal delete so it would go something like
this
delete from customers where id=5 and user='chris'
Now all that is needed to delete from customers is the id
So in my delete trigger I want to parse off the user and actually use
it as a value in the insert for the history table
So in the actual delete trigger itself it would get the " and
user='chris' " stripped off an the delete would just be
delete from customers where id=5
I would then use the user='chris' in the insert of the history table.
I cant really use a sproc, as the code calling this cant easily be
changed, but I still need to do this on the delete.
SO , How can I get the value of user , OR The whole sql that is causing
the trigger to fire ?
Many Thanks
Chris Wertman 4 1287
On 2/9/05 10:32 PM, in article 11**********************@f14g2000cwb.googlegroups. com, "WertmanTheMad"
<cw******@webchamps.com> wrote: Ok here goes, another odd SQL Question ....as always..
How do I get ... The value of a paramater passed to say a Trigger OR The SQL Itself
Like this , say I have a Trigger set on delete,
Now this trigger will add a row to another table (a history table) but it need to be passed a paramater, throught the sql itself as it cannot be any different than a normal delete so it would go something like this
delete from customers where id=5 and user='chris'
Now all that is needed to delete from customers is the id
So in my delete trigger I want to parse off the user and actually use it as a value in the insert for the history table
So in the actual delete trigger itself it would get the " and user='chris' " stripped off an the delete would just be
delete from customers where id=5
I would then use the user='chris' in the insert of the history table.
I cant really use a sproc, as the code calling this cant easily be changed, but I still need to do this on the delete.
SO , How can I get the value of user , OR The whole sql that is causing the trigger to fire ?
Many Thanks
Chris Wertman
All you really need to do in the delete trigger is query deleted data for
the fields you would like to use in your insert statement...
DECLARE @user varchar(16)
SELECT @user = user FROM deleted
INSERT INTO history (user, deleteddate) VALUES (@user, GETDATE())
-Greg
WertmanTheMad wrote: Ok here goes, another odd SQL Question ....as always..
How do I get ... The value of a paramater passed to say a Trigger OR The SQL Itself
Like this , say I have a Trigger set on delete,
Now this trigger will add a row to another table (a history table)
but it need to be passed a paramater, throught the sql itself as it
cannot be any different than a normal delete so it would go something like this
delete from customers where id=5 and user='chris'
Now all that is needed to delete from customers is the id
So in my delete trigger I want to parse off the user and actually use it as a value in the insert for the history table
So in the actual delete trigger itself it would get the " and user='chris' " stripped off an the delete would just be
delete from customers where id=5
I would then use the user='chris' in the insert of the history table.
I cant really use a sproc, as the code calling this cant easily be changed, but I still need to do this on the delete.
SO , How can I get the value of user , OR The whole sql that is
causing the trigger to fire ?
Many Thanks
Chris Wertman
If user is part of the table you are deleting from and id is a primary
key, then you can just pick up the user from the deleted table within
the trigger.
If user is not part of the table you are deleting from, or if the user
might not be the value of the user table, then you would be better off
deleting using a stored procedure as you cannot change the statement
once the trigger has been called.
In other words, if user 'John' added id 5 and you have the value 'John'
in the table then you would not have any rows in the deleted table to
be removed.
If all users log on with their own uid then you could use the
suser_sname() function to get the current user name instead.
--
David Rowland http://dbmonitor.tripod.com
Try DBMonitor for a good performance and activity monitor
If all you want to do is copy the deleted rows to the history table,
then you can just INSERT the contents of the deleted table - see "Using
the inserted and deleted Tables" in Books Online.
create trigger dbo.MyTrig
on dbo.MyTable
after delete
as
insert into dbo.HistoryTable (col1, col2, ...)
select col1, col2, ...
from deleted
Simon
If you use DELETE is deletes the entire row, not the specified filed. If you
just want to NULL the field use UPDATE SET CUSTOMERS = NULL WHERE ID=5;.
I would suggest using INSTED OF TRIGGER (INSTEAD OF DELETE ...), look http://msdn.microsoft.com/library/de...es_08_49kj.asp.
To pass arguments to TRIGGER you have two tables deleted and inserted which
you can use (in some database system NEW and OLD as new and old record),
look http://msdn.microsoft.com/library/de...es_08_0lo3.asp.
Hope it helps. If something is unclear, make a reply.
"Gregory Dean" <gd***@datapex.com> wrote in message
news:BE3047C0.4779%gd***@datapex.com... On 2/9/05 10:32 PM, in article 11**********************@f14g2000cwb.googlegroups. com, "WertmanTheMad" <cw******@webchamps.com> wrote:
Ok here goes, another odd SQL Question ....as always..
How do I get ... The value of a paramater passed to say a Trigger OR The SQL Itself
Like this , say I have a Trigger set on delete,
Now this trigger will add a row to another table (a history table) but it need to be passed a paramater, throught the sql itself as it cannot be any different than a normal delete so it would go something like this
delete from customers where id=5 and user='chris'
Now all that is needed to delete from customers is the id
So in my delete trigger I want to parse off the user and actually use it as a value in the insert for the history table
So in the actual delete trigger itself it would get the " and user='chris' " stripped off an the delete would just be
delete from customers where id=5
I would then use the user='chris' in the insert of the history table.
I cant really use a sproc, as the code calling this cant easily be changed, but I still need to do this on the delete.
SO , How can I get the value of user , OR The whole sql that is causing the trigger to fire ?
Many Thanks
Chris Wertman
All you really need to do in the delete trigger is query deleted data for the fields you would like to use in your insert statement...
DECLARE @user varchar(16) SELECT @user = user FROM deleted INSERT INTO history (user, deleteddate) VALUES (@user, GETDATE())
-Greg This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: David |
last post by:
hello.
when doing the simple following computation, the value put into the variable
numMinusOne is NOT the same as what the computation is showed to be in the
Watch window!!
here is the code:...
|
by: Rob Ristroph |
last post by:
Hi,
It's pretty unhelpful to post "I have a huge piece of code that
crashes in strange places, what's the problem?" but that's basically
my problem and I really am at my wit's end.
The piece...
|
by: FrankEsser |
last post by:
Hello!
I am not an expert on C++ programming and therefor I have a question:
We use a kind of communication server that was written in C++
especially for our company. It just takes incoming...
|
by: Werner Partner |
last post by:
I use a page created by php.
It should show two pictures of a person an a short text.
If there are no picture, nothing is shown, if there is no text, nothing
is shown.
There a about 20...
|
by: Richard Hollenbeck |
last post by:
I've asked this question before but over a month later I still don't have an
answer. A few people did try to help and I am thankful, but I really didn't
get the problem solved. I'll try to ask...
|
by: unknown |
last post by:
Hi,
I am developing an online book store with shopping cart.
My shopping cart is represented as a Xml server control
and I am using an XSLT to render it at the client side.
I am using an...
|
by: Mike C# |
last post by:
Hi all,
I keep getting a strange error and can't pin it down. The message is:
This application has requested the Runtime to terminate it in an unusual
way.
Please contact the application's...
|
by: zacks |
last post by:
I am developing an app in VS2005 (actually in VB.NET but this
question, I believe, would apply to any .NET language) that is used to
design the contents of an XML file. One of potential items that...
|
by: |
last post by:
I have signed up to a dotnet hosting account and I have a dotnet app
called CommunityServer, which allows me to setup my own news
forums. The path of one of my forums (the third one) is
...
|
by: Heinrich Pumpernickel |
last post by:
i got the following strange extra question in a written test
today . since the test was abt c/c++ i guess this is on topic here
--8<--
22) Consider the following quote:
"If Mickey's a...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
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,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
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,...
|
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...
|
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,...
| |