getting a constraint value from query in a trigger?

say i have a query like:

UPDATE table SET status = 1 WHERE id = 1000

if i have a trigger on that table, is there anyway i can get the id ?

i know i can get the status by SELECT status FROM Inserted, but anyway to get what the id is? or would i just have to update the id as well?

thankshmm, looks like i can't even get the id if i try to update since it's an identity column|||How about:SELECT id FROM inserted-PatP|||inserted AND deleted are full blown copies of the table that's affected for the rows that are be modified or added.

You [Id] should be there

What are you trying to do?|||oops, actually i just realized SELECT id from inserted works fine even though id isn't updated

thanks

Getdate() in UDF column workaround

I have a column that needs to display the number of Status hours between
Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usin
g
an Access project as my front end and SQL Server 2000 as my back end. I have
tried using the following as a row source in my function:
CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
like that. How can I display the status time on my form? I was thinking mayb
e
the text box record source could be a select statement but not sure how to
write it, any ideas?DateDiff(HH, STATUS_START_DATE + STATUS_START_TIME,
COALESCE(STATUS_STOP_DATE + STATUS_STOP_TIME,
GETDATE())
Roy
On Sat, 4 Mar 2006 14:08:27 -0800, AkAlan
<AkAlan@.discussions.microsoft.com> wrote:

>I have a column that needs to display the number of Status hours between
>Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am usi
ng
>an Access project as my front end and SQL Server 2000 as my back end. I hav
e
>tried using the following as a row source in my function:
>CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
>STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
>I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
>like that. How can I display the status time on my form? I was thinking may
be
>the text box record source could be a select statement but not sure how to
>write it, any ideas?|||Hi
CREATE FUNCTION dbo.Get_Getdate
(@.dt DATETIME)
RETURNS DATETIME
AS
BEGIN
RETURN @.dt
END
SELECT dbo.Get_Getdate (GETDATE())
SELECT dbo.Get_Getdate ('20050101')
"AkAlan" <AkAlan@.discussions.microsoft.com> wrote in message
news:30B29109-1B8E-4716-A506-EEDB943F4B64@.microsoft.com...
>I have a column that needs to display the number of Status hours between
> Start_Date and either Stop_Date or Getdate() if StopDate is empty. I am
> using
> an Access project as my front end and SQL Server 2000 as my back end. I
> have
> tried using the following as a row source in my function:
> CASE WHEN STATUS_STOP_DATE IS NULL THEN datediff([HH] , STATUS_START_DATE
> +
> STATUS_START_TIME , Getdate()) ELSE datediff([HH] , STATUS_START_DATE +
> STATUS_START_TIME , STATUS_STOP_DATE + STATUS_STOP_TIME) END
> I get an Invalid use of Getdate() in a function. Ok so I can't use getdate
> like that. How can I display the status time on my form? I was thinking
> maybe
> the text box record source could be a select statement but not sure how
> to
> write it, any ideas?sql

Get records count from SQL cursor

Have you thought of declaring a int then adding one to it
per record ?
Peter
"Status quo, you know, that is Latin for "the mess we're
in."
Ronald Reagan

>--Original Message--
>Hi experts,
>I have created a SQL cursor for records processing in a
stored procedure. I
>probably can use the @.@.Cursor_Rows function in order to
obtain total rows of
>record contained inside the cursor. But once I declare
the cursor as
>FAST_FORWARD, it always return me -1. I need to declare
the cursor as
>FAST_FORWARD as it really helps in tuning the
performance. Else my stored
>procedure will take longer time to execute.
>Any other way I can use to get the total records being
returned? I've tried
>to signal another SQL statement to perform the records
count but this seems
>to create redundant overhead. I believe if I can do
anything to existing
>cursor without having extra Select Count statement, it
would help to reduce
>unnecessary processing and shorten the overall time
required.
>Really appreciate for any advice or suggestion. Thanks a
lot.
>.
>I've tested to append 1 to an int variable each time while looping the curso
r
but the effect is not so significant to boost the performance...Anyway,
thanks for the suggestion...
"Peter The Spate" wrote:

> Have you thought of declaring a int then adding one to it
> per record ?
> Peter
> "Status quo, you know, that is Latin for "the mess we're
> in."
> Ronald Reagan
>
> stored procedure. I
> obtain total rows of
> the cursor as
> the cursor as
> performance. Else my stored
> returned? I've tried
> count but this seems
> anything to existing
> would help to reduce
> required.
> lot.
>

Get Query Status

I need a way to determine if a query is still running on the database server. Is there a way to do this?

If the query is still running after 2 hours I need to send a message. How can I query the database for the status of a query?

This will give you an idea of what queries are currently running on your server, and what the query is:

select percent_complete,* from sys.dm_exec_requests
cross apply sys.dm_exec_sql_text(sql_handle)
where session_id > 50

This will tell you the longest open tran in the db:

DBCC OPENTRAN|||

If you know the session ID in question, then you can use this statement posted today by Louis Davidson.

declare @.spid int

set @.spid = ?

select

der.session_id,

der.wait_type,

der.wait_time,

der.status as requestStatus,

des.login_name,

cast(db_name(der.database_id) as sysname) as databaseName,

des.program_name,

der.command as commandType,

execText.text as objectText,

case when der.statement_end_offset = -1 then '--see objectText--'

else SUBSTRING(execText.text, der.statement_start_offset/2,

(der.statement_end_offset - der.statement_start_offset)/2)

end AS currentExecutingCommand,

der.open_transaction_count

from

sys.dm_exec_sessions des

inner join

sys.dm_exec_requests as der

on der.session_id = des.session_id

cross apply

sys.dm_exec_sql_text(der.sql_handle) as execText

where

des.session_id = @.spid;

Check BOL for more info about these DMVs.

AMB