Passing information from client to SQL Server

Is there a means to pass information to SQL server (on a single connection)
that is accessible from T-SQL or triggers. My scenario is that when certain
tables are updated/inserted/deleted, I am making entries in a "log" table,
via triggers, that contains the table name and the action performed on it.
What I also need to insert into the logging table is an integer denoting som
e
additional information about the update/insert/delete which needs to come
from the client. Also, this information needs to be scoped to the connectio
n
so that it is not accesible from other connections.What is the additional info? Some things come for free that might be useful
for your scenario eg HOST_NAME(), HOST_ID() but if this is something
specific to your business, then perhaps it needs to be an additional field
on each table, and the triggers will pick it up that way.
Cheers,
Paul Ibison|||Maybe using context_info is an option you can use in your
scenario. Refer to the following books online topic for more
info:
SQL 2000
http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
or
SQL 2005
http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
-Sue
On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
<arothberg@.newsgroups.nospam> wrote:

>Is there a means to pass information to SQL server (on a single connection)
>that is accessible from T-SQL or triggers. My scenario is that when certai
n
>tables are updated/inserted/deleted, I am making entries in a "log" table,
>via triggers, that contains the table name and the action performed on it.
>What I also need to insert into the logging table is an integer denoting so
me
>additional information about the update/insert/delete which needs to come
>from the client. Also, this information needs to be scoped to the connecti
on
>so that it is not accesible from other connections.|||This is exactly what I needed. Thanks.
"Sue Hoegemeier" wrote:

> Maybe using context_info is an option you can use in your
> scenario. Refer to the following books online topic for more
> info:
> SQL 2000
> http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
> or
> SQL 2005
> http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
> -Sue
> On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
> <arothberg@.newsgroups.nospam> wrote:
>
>

Passing information from client to SQL Server

Is there a means to pass information to SQL server (on a single connection)
that is accessible from T-SQL or triggers. My scenario is that when certain
tables are updated/inserted/deleted, I am making entries in a "log" table,
via triggers, that contains the table name and the action performed on it.
What I also need to insert into the logging table is an integer denoting some
additional information about the update/insert/delete which needs to come
from the client. Also, this information needs to be scoped to the connection
so that it is not accesible from other connections.What is the additional info? Some things come for free that might be useful
for your scenario eg HOST_NAME(), HOST_ID() but if this is something
specific to your business, then perhaps it needs to be an additional field
on each table, and the triggers will pick it up that way.
Cheers,
Paul Ibison|||Maybe using context_info is an option you can use in your
scenario. Refer to the following books online topic for more
info:
SQL 2000
http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
or
SQL 2005
http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
-Sue
On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
<arothberg@.newsgroups.nospam> wrote:
>Is there a means to pass information to SQL server (on a single connection)
>that is accessible from T-SQL or triggers. My scenario is that when certain
>tables are updated/inserted/deleted, I am making entries in a "log" table,
>via triggers, that contains the table name and the action performed on it.
>What I also need to insert into the logging table is an integer denoting some
>additional information about the update/insert/delete which needs to come
>from the client. Also, this information needs to be scoped to the connection
>so that it is not accesible from other connections.|||This is exactly what I needed. Thanks.
"Sue Hoegemeier" wrote:
> Maybe using context_info is an option you can use in your
> scenario. Refer to the following books online topic for more
> info:
> SQL 2000
> http://msdn2.microsoft.com/en-US/library/aa214382(sql.80).aspx
> or
> SQL 2005
> http://technet.microsoft.com/en-us/library/ms189252(SQL.90).aspx
> -Sue
> On Sun, 17 Jun 2007 09:34:00 -0700, arothberg
> <arothberg@.newsgroups.nospam> wrote:
> >Is there a means to pass information to SQL server (on a single connection)
> >that is accessible from T-SQL or triggers. My scenario is that when certain
> >tables are updated/inserted/deleted, I am making entries in a "log" table,
> >via triggers, that contains the table name and the action performed on it.
> >What I also need to insert into the logging table is an integer denoting some
> >additional information about the update/insert/delete which needs to come
> >from the client. Also, this information needs to be scoped to the connection
> >so that it is not accesible from other connections.
>

Pass Variables from Trigger to SP ?

Fairly new to triggers and stored procedures and so far I haven't had to pass any variables or even call an SP from a trigger. The problem I have is to pass a 'callid' variable from an insert trigger to a stored procedure.

The SP is to delete any rows that already exist that contain the passed 'callid' and then insert the new inserted data (using variables again hopefully).

Is this at all possible and if so what is the syntax for passing the variable from the trigger and then reading it into the stored procedure ?

TIASomething like:

CREATE TRIGGER trg_mytrigger ON tbl_my_table INSEAD OF INSERT
AS

DECLARE @.callid INT

SELECT @.callid = callid FROM inserted

--At this point, you can just to the delete from here, rather than calling a seperate sp_, so here are both ways:
EXEC sp_delete_rows_by_callid @.callid

--or
DELETE tbl_my_table WHERE callid = @.callid

--then do the insert - (you may want to be more explicit with field names here)

INSERT tbl_my_table
SELECT * FROM inserted

--end of trigger

sp_code:

CREATE PROCEDURE sp_delete_rows_by_callid
@.callid INT
AS
DELETE tbl_my_table WHERE callid = @.callid

-b|||Thanks bpdWork. I have modified the trigger as you suggested but I get an ODBC 3146 database error. Can you see where I may have got the syntax wrong. Thx.

CREATE TRIGGER trg_mytrigger ON callshistory INSTEAD OF INSERT
AS

DECLARE @.callid INT

SELECT @.callid = fkey FROM inserted as ins where ins.actiontext = 'hold' and ins.Subactiontext in ('completed','pending user')

DELETE hold_complete WHERE fkey = @.callid

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
insert hold_complete
select ins.AddedDT, ins.fkey, ins.actiontext,
ins.subactiontext, con.emailaddress, ca.loggeddt,
(con.forename + ' ' + con.surname) as contactname,
ca.summary, ca.notes,co.coordinator, co.coordinator,getdate(), ca.status,ca.lastsubaction,getdate(),ca.dateopened ,ca.companyname,getdate(),(null),ch.notes
FROM inserted as ins with (nolock)
join calls as ca with (nolock)on
ins.fkey = ca.callid
join contact as con with (nolock) on
ca.contactid = con.contactid
join company as co with (nolock) on
ca.companyid = co.companyid
join callshistory as ch with (nolock) on
ins.historyid = ch.historyid
where ins.actiontext = 'hold' and ins.Subactiontext in ('completed','pending user')|||I believe this error means Invalid Object Name, so the problem is probably one of your table names.

I'm not sure you can alias the "inserted" keyword. Try it without that.|||The syntax from SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED was used in my original trigger which allowed the duplicates so I know that bit works. I also tried the SP option you suggested and got no error message but no rows were deleted or inserted.

Can the inserted table be used outside of the original trigger for the SP ? When does it get deleted ?|||It's only around for the virtual instance the trigger is fired...once the trigger is done, it's gone...|||So should this work ? (BTW it don't.)

CREATE TRIGGER tr_DELETE_DUPES ON Hold_Complete
after insert AS

DECLARE @.callid varchar

SELECT @.callid = fkey FROM inserted

delete from hold_complete where addeddt <> (select max(addeddt)from hold_complete) and fkey = @.callid|||delete from hold_complete where addeddt NOT IN (select max(addeddt)from hold_complete) and fkey = @.callid|||I'm afraid that didn't delete the dupe rows either. Is there anyway that a message can be printed that will show the @.callid variable to make sure that's being captured properly|||You need to understand that the virtual tables will hold ALL of the affected rows...not just 1...your code will only grab the last one from the table...

you need to think set based...

And if you trying to DELETE dups AFTER the INSERT, I would think you would gewt a dup key error, and the trans would rollback and the trigger won't fire...

Assuming you have a pk contraint that is...

Also I would recommend removing the ISOLATION LEVEL code...

If you check @.@.ERROR in the sproc that is doing the INSERT you'll catch the error there...

You can then do an ipdate if you want to change the values...

I wouldn't do this in a trigger (Unless of course you don't have control over the DML, then I would)...|||CREATE TRIGGER tr_DELETE_DUPES ON Hold_Complete
after insert AS

DELETE
FROM hold_complete
INNER JOIN inserted
ON hold_complete.fkey = inserted.fkey
WHERE addeddt <> (select max(addeddt) from hold_complete)|||Brett

The hold_complete table is updated from an insert trigger on another table when certain criteria are met. This is unlikely to insert more than 1 row at a time. How would you suggest I go about deleting any previous entries with the same callid ?

bpd

It didn't like the join on a delete statement.|||My Bad:

DELETE hold_complete
FROM hold_complete
INNER JOIN inserted
ON hold_complete.fkey = inserted.fkey
WHERE addeddt <> (select max(addeddt) from hold_complete)|||Originally posted by Bracksboy
Brett

The hold_complete table is updated from an insert trigger on another table when certain criteria are met. This is unlikely to insert more than 1 row at a time. How would you suggest I go about deleting any previous entries with the same callid ?

bpd

It didn't like the join on a delete statement.

Cascading triggers?

Also it's not a matter of how likely an event can happen...it's whether it can or it can't...there are no colors here...it's either black or white...

It's not a matter of IF, it's a matter of WHEN

First, do you have a Primary Key contraint on CallId now?

If not, first find out what you're dealing with...

SELECT CallId, COUNT(*) FROM hold_complete GROUP BY CallId HAVING COUNT(*) > 1

If that returns nothing, you're in business...and just add the contraint, and you won't have to worry about the trigger at all...

If it does, then you need to sanatize the data, then add the contraint...|||Originally posted by Brett Kaiser

First, do you have a Primary Key contraint on CallId now?

If not, first find out what you're dealing with...

SELECT CallId, COUNT(*) FROM hold_complete GROUP BY CallId HAVING COUNT(*) > 1

If that returns nothing, you're in business...and just add the contraint, and you won't have to worry about the trigger at all...

If it does, then you need to sanatize the data, then add the contraint...

I don't have a constraint on callid and I don't think it will work with this. If I understand constraints correctly if I set one on callid it won't allow an insert where that callid already exists. Is that correct ?

The inserted data is not going to always have a unique callid so I just need to store the latest row of data and remove all previous rows with that callid. The rows that are inserted are tracking details from a call logging system that meet certain criteria and this can have multiple rows with the same callid. From this data I have a VB app that emails confirmation and then sets dates for chasers and automated closure. I need to delete the existing rows so only the latest info is used in the app.

It's all very new to me.|||You could always just do the delete first, then do an insert.|||but that's the whole point of a realtional database...

You should check to see if the row exists first...if it does grab it, and update it...

Then there's the problem if the row doesn't exist, you start enetering info, someone slides the key while you're typing and get it in...

You need to error handle and check @.@.Error to see if the execution was successful or not....

no MOO's about it...|||Thanks for your help guys but had a complete rethink and I have the application sorting the records I want using the following select statement in the ADO connection :

SELECT * FROM hold_complete a
WHERE exists (SELECT fkey FROM hold_complete b
GROUP BY fkey HAVING max(b.addeddt) = a.addeddt)
ORDER by fkey