Strange SQL Question

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

re: Strange SQL Question

On 2/9/05 10:32 PM, in article
1108006343.442733.104310@f14g2000cwb.googlegroups. com, "WertmanTheMad"
<cwertman@webchamps.com> wrote:
[color=blue]
> 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
>[/color]

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

re: Strange SQL Question

WertmanTheMad wrote:[color=blue]
> 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)[/color]
but[color=blue]
> it need to be passed a paramater, throught the sql itself as it[/color]
cannot[color=blue]
> 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[/color]
causing[color=blue]
> the trigger to fire ?
>
> Many Thanks
>
> Chris Wertman[/color]

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

re: Strange SQL Question

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

re: Strange SQL Question

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" <gdean@datapex.com> wrote in message
news:BE3047C0.4779%gdean@datapex.com...[color=blue]
> On 2/9/05 10:32 PM, in article
> 1108006343.442733.104310@f14g2000cwb.googlegroups. com, "WertmanTheMad"
> <cwertman@webchamps.com> wrote:
>[color=green]
>> 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
>>[/color]
>
> 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
>[/color]