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

Get value of parameters passed in stored procedure in a trigger

In updating my tables (insert/update), i use stored procedures.
Some of the values passed as parameters to the stored procedures
are only necessary for audit trail only and not for updating the tables.
How can i get hold of these parameter values while inside a trigger?Put the parameters in a permanent table or a local temp table.
David Portas
SQL Server MVP
--
"manK" <manK@.discussions.microsoft.com> wrote in message
news:EA0833BF-A070-4720-9E50-9C80EAE45FF9@.microsoft.com...
> In updating my tables (insert/update), i use stored procedures.
> Some of the values passed as parameters to the stored procedures
> are only necessary for audit trail only and not for updating the tables.
> How can i get hold of these parameter values while inside a trigger?
>

get triggers latest update date

Is there a way in MSSQL 2000 to get trigger's latest update date?
sysobject table only has creation date of a trigger and I've been using ALTER TRIGGER command to modify it.
Thanks,
IgorUnfortunately, no. You can't get the last modified date for any SQL Server objects. You might instead do a DROP and CREATE when modifying your triggers.

Terri

get trigger schema within CLR code

Hi.

I am trying to get the schema in which the trigger is created within the CLR code.

1)create a new schema MySchema.

2) created a table MySchema.MyTable

3) created the assembly and trigger ( create trigger MySchema.MyTrigger on MySchema.MyTable..... ) Trigger writes to another table MySchema.MyLog.

Code works fine if I hardcode Myschema.MyLog in the CLR but fails when I say just MyLog.

So how do dynamically get the trigger's schema name ?

Thanks for your help.

Nach

There is no way to get the schema of the currently executing trigger from clr. With T-SQL, you code would have to create dynamic sql based on the current trigger, or rely on the implict schema name binding that occurs.

There are alternatives
1. Use execute as user and have the specified user have a default schema of MySchema
2. Create a T-SQL trigger that calls your clr trigger converted to a proc, passing it the @.@.ProcId of the T-SQL trigger as a parameter, and you can then get object_name & schema id from the clr procedure

Get trigger information

Hi Freinds,
SQL 2000
I need to find out that if any of triggers on my database has a word
"dup_order" in it
is ther eany schema lime infromation_schema.column_name that I can use to
scan all triggers and find out which one contains the word?
Thanks in advance,
PatYou can search syscomments system table which has the source code for all
the user defined triggers in your database.
Anith|||SELECT OBJECT_NAME(id)
FROM syscomments
WHERE OBJECTPROPERTY(id, 'IsTrigger')=1
AND [text] LIKE '%dup[_]error%'
Because triggers that are > 8000 characters will span multiple rows, and
since the text "dup_order" might only appear in such a position that it
straddles rows, a better way might be to have all your triggers scripted out
using Enterprise Manager, and do a search within the output.
I am not sure exactly how SQL Server internally maps a trigger to belong to
a certain table (it may be embedded in the hideous ctext column somehow?).
You can certainly read/parse the ON section of the trigger, and that will
tell you, but doing so programmatically might not be 100% effective.
In SQL Server 2005 we will have more foolproof methods for scanning the
entire definition of an object, rather than deal with this 8000-character
chunk limitation.
"Patrick" <patriarck@.gmail.com> wrote in message
news:uU1sk%230oFHA.3304@.tk2msftngp13.phx.gbl...
> Hi Freinds,
> SQL 2000
> I need to find out that if any of triggers on my database has a word
> "dup_order" in it
> is ther eany schema lime infromation_schema.column_name that I can use to
> scan all triggers and find out which one contains the word?
> Thanks in advance,
> Pat
>|||To overcome the 4000 characters limitation, use the following procedure
to search for a string in all views, procedure, triggers and UDF-s:
CREATE PROCEDURE sp_findtext(@.Text nvarchar(256))
AS
SET @.Text=Replace(Replace(Replace(@.Text,
'[', '[[]'), '%', '[%]'), '_', '[_]')
SELECT name, xtype FROM (
SELECT id FROM syscomments WHERE text LIKE '%'+@.Text+'%'
UNION
SELECT c1.id FROM syscomments c1
INNER JOIN syscomments c2 ON c1.id=c2.id AND c1.colid=c2.colid-1
WHERE SUBSTRING(c1.text,3001,1000)+LEFT(c2.text,1000)
LIKE '%'+@.Text+'%'
) U INNER JOIN sysobjects o ON u.id=o.id
ORDER BY xtype, name

> I am not sure exactly how SQL Server internally maps
> a trigger to belong to a certain table
See the parent_obj column in the sysobjects table.
Razvan

Get tablename in trigger

Hello,
I have a general trigger program used for many tables, but how do I refer to
the tablename currently been modified in my script?
Thanks!
PerCREATE TABLE TT
(
COL INT
)
CREATE TRIGGER MY_TR ON TT
FOR INSERT
AS
DECLARE @.ObjID int
SET @.ObjID = (SELECT parent_obj FROM sysobjects WHERE id = @.@.PROCID)
SELECT OBJECT_NAME(@.ObjID) AS 'Parent Table'
INSERT INTO TT VALUES (1)
SELECT * FROM TT
DROP TABLE TT
"Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
message news:99BA6A44-27DC-4D58-8794-7A90F24A2B8F@.microsoft.com...
> Hello,
> I have a general trigger program used for many tables, but how do I refer
> to
> the tablename currently been modified in my script?
> Thanks!
> Per
>|||Working perfect :-)
Thanks!
"Uri Dimant" wrote:

> CREATE TABLE TT
> (
> COL INT
> )
> CREATE TRIGGER MY_TR ON TT
> FOR INSERT
> AS
> DECLARE @.ObjID int
> SET @.ObjID = (SELECT parent_obj FROM sysobjects WHERE id = @.@.PROCID)
> SELECT OBJECT_NAME(@.ObjID) AS 'Parent Table'
> INSERT INTO TT VALUES (1)
> SELECT * FROM TT
> DROP TABLE TT
>
>
> "Per Buus S?rensen" <PerBuusSrensen@.discussions.microsoft.com> wrote in
> message news:99BA6A44-27DC-4D58-8794-7A90F24A2B8F@.microsoft.com...
>
>|||I'm not certain what you mean by a "general trigger program". If you
mean you are generating and re-using the same code for each table then
surely you would put the table name in there when you generate the
code, in which case there would be no need to do it dynamically at
runtime. That's the method I would recommend anyway.
If you mean you are calling the same proc from each trigger then Uri's
code won't help you. In that case I think you will have to pass the
table name as a parameter.
David Portas
SQL Server MVP
--|||The code was OK, I am writting one procedure which can apply to many tables,
however I have a issue with dynamic SQL, which I have put in a new post.
Per
"David Portas" wrote:

> I'm not certain what you mean by a "general trigger program". If you
> mean you are generating and re-using the same code for each table then
> surely you would put the table name in there when you generate the
> code, in which case there would be no need to do it dynamically at
> runtime. That's the method I would recommend anyway.
> If you mean you are calling the same proc from each trigger then Uri's
> code won't help you. In that case I think you will have to pass the
> table name as a parameter.
> --
> David Portas
> SQL Server MVP
> --
>

Get return value from stored procedure and trigger

Hello there,

I searched for answers to the above topic, but could not find what I want. My stored procedures and triggers are returning a message based on the result, mostly error messages. How can I get that message using ASP.Net? Should I use an output parameter?

Thank you for your help.

Use this type dataset generator it will do it for you. you select which sp to use with a gui and paf you get a nice typed dataset class.

Enjoy I love it greatly

SQL Stored Procedure Wrapper & Typed DataSet Generator for .NET...

DBHelper is a small tool that will generate either a source file or a compiled... No problem forDBHelper, just a few clicks and you a have all the methods...
www.codeproject.com/cs/database/dbhelper.asp

|||

Joel,

Thank you for the reply and a looking great tool. I will try it. I am not sure thought if it will answer, but trust you.

Another way I just found is that using Exception class in an event in .Net. For example, if you delete a row in a gridview and trigger is fired for some reason and raises an error, it is passed to theGridViewDeletedEventArgs.

So you can that message withGridViewDeletedEventArgs.Exception.Message. You can do it GridView1_RowDeleted() event.

I hope this helps someone like me.