How to know if INSERTing or UPDATing?

Hi all.
I am quite new to SQLServer 2005 and I cannot seem to solve it.
If we have a INSERT, UPDATE trigger, how do we know if the action that
fired it is an INSERT or an UPDATE?
Looking at 'deleted' virtual values perhaps?
COLUMNS_UPDATED() yields > 0 even for INSERTS.
TIA
Cheers.
Carlos.
Carlos,
Rows in inserted and deleted = UPDATE
Rows in inserted only = INSERT
Rows in deleted only = DELETE
BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
because the columns have either explicit values or implicit (NULL) values
inserted.
RLF
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181228538.517937.317920@.o5g2000hsb.googlegro ups.com...
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
>
|||> Looking at 'deleted' virtual values perhaps?
Yes,
if exists (select 1 from deleted) and exists (select 1 from inserted)
' update
|||On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:[vbcol=seagreen]
> Carlos,
> Rows in inserted and deleted = UPDATE
> Rows in inserted only = INSERT
> Rows in deleted only = DELETE
> BOL says: COLUMNS_UPDATED returns TRUE for all columns in INSERT actions
> because the columns have either explicit values or implicit (NULL) values
> inserted.
> RLF
> "CarlosAL" <miotromailcar...@.netscape.net> wrote in message
> news:1181228538.517937.317920@.o5g2000hsb.googlegro ups.com...
>
>
>
>
Yep!
I was suspecting someting like that...
Thank you so much Russell.
Its a long long way to go from Oracle to SQL Server...
Cheers.
Carlos.
|||On 7 jun, 17:23, CarlosAL <miotromailcar...@.netscape.net> wrote:
> On 7 jun, 17:16, "Russell Fields" <russellfie...@.nomail.com> wrote:
>
>
>
>
>
>
>
>
> Yep!
> I was suspecting someting like that...
> Thank you so much Russell.
> Its a long long way to go from Oracle to SQL Server...
> Cheers.
> Carlos.
...and thank you Aaron too! (You posted while I was writing)
Cheers.
Carlos
|||On Jun 7, 8:02 pm, CarlosAL <miotromailcar...@.netscape.net> wrote:
> Hi all.
> I am quite new to SQLServer 2005 and I cannot seem to solve it.
> If we have a INSERT, UPDATE trigger, how do we know if the action that
> fired it is an INSERT or an UPDATE?
> Looking at 'deleted' virtual values perhaps?
> COLUMNS_UPDATED() yields > 0 even for INSERTS.
> TIA
> Cheers.
> Carlos.
I think checking rows in inserted and deleted is a crude way . If
Trigger does not insert or update rows in the table due to some
where condition, these system tables will not have any rows. Some
times it may be required to capture the event .
MS should provide a function to capture which one of the INSERT/UPDATE/
DELETE events prompted trigger to execute
|||> I think checking rows in inserted and deleted is a crude way . If
> Trigger does not insert or update rows in the table due to some
> where condition, these system tables will not have any rows.
And so any IF EXISTS check will return false. Anyway, your trigger should
have IF @.@.ROWCOUNT > 0 before executing anything anyway. That way if no
rows are affected, the trigger exits cleanly.
|||On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:

>Hi all.
>I am quite new to SQLServer 2005 and I cannot seem to solve it.
>If we have a INSERT, UPDATE trigger, how do we know if the action that
>fired it is an INSERT or an UPDATE?
Hi Carlos,
If the trigger has to do different things on insert and update, why not
create two triggers? Saves you the need to find out what the action was,
and might result in better performance to boot!
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
|||On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
wrote:
> On Thu, 07 Jun 2007 08:02:18 -0700, CarlosAL wrote:
>
> Hi Carlos,
> If the trigger has to do different things on insert and update, why not
> create two triggers? Saves you the need to find out what the action was,
> and might result in better performance to boot!
> --
> Hugo Kornelis, SQL Server MVP
> My SQL Server blog:http://sqlblog.com/blogs/hugo_kornelis
Thanks to all.
Hugo: The problem here is the developer (not me) decided to implement
TWO triggers in order to provide values for audit columns. If we have
two triggers (basically they do the same thing), when we UPDATE the
(auditory) values in an insert, we fire the second trigger, which is
not desiseable: two triggers to do one (same) thing.
Cheers and thanks again.
Carlos.
|||You can easily add logic to your update stored procedure that ignores
updates that affect the auditing columns. Have a look at IF
UPDATE(column_name) ...
"CarlosAL" <miotromailcarlos@.netscape.net> wrote in message
news:1181544855.466045.265580@.q75g2000hsh.googlegr oups.com...
> On 9 jun, 23:33, Hugo Kornelis <h...@.perFact.REMOVETHIS.info.INVALID>
> wrote:
> Thanks to all.
> Hugo: The problem here is the developer (not me) decided to implement
> TWO triggers in order to provide values for audit columns. If we have
> two triggers (basically they do the same thing), when we UPDATE the
> (auditory) values in an insert, we fire the second trigger, which is
> not desiseable: two triggers to do one (same) thing.
> Cheers and thanks again.
> Carlos.
>

How to know if a user has UNSAFE ASSEMBLY permission

Hi All,

How can I know if a user has UNSAFE ASSEMBLY permission on master database by executing query.

Please assist its really urgent, as I m stuck on this.

See SQL Server 2005 Books Online:
ALTER ASSEMBLY (Transact-SQL)
http://msdn2.microsoft.com/en-us/library/ms186711.aspx

Creating an Assembly

http://msdn2.microsoft.com/en-us/library/ms345106.aspx

Users don’t have UNSAFE ASSEMBLY permission on master database but maybe you are looking for this:

SELECT pr.name

FROM sys.server_principals pr

INNER JOIN sys.server_permissions pe

ON pr.principal_id = pe.grantee_principal_id

WHERE pe.permission_name = 'EXTERNAL ACCESS ASSEMBLY'

If you want to change permision on as assembly:

To alter an assembly whose existing permission set is UNSAFE requires membership in the sysadmin fixed server role.

To change the permission set of an assembly to UNSAFE requires membership in the sysadmin fixed server role.

If you are creating assemblies:

We recommend that the TRUSTWORTHY Database Property on a database not be set to ON only to run common language runtime (CLR) code in the server process. Instead, we recommend that an asymmetric key be created from the assembly file in the master database. A login mapped to this asymmetric key must then be created, and the login must be granted EXTERNAL ACCESS ASSEMBLY or UNSAFE ASSEMBLY permission.

USE master GOCREATE ASYMMETRIC KEY SQLCLRTestKey FROM EXECUTABLE FILE = 'C:\MyDBApp\SQLCLRTest.dll'CREATE LOGIN SQLCLRTestLogin FROM ASYMMETRIC KEY SQLCLRTestKeyGRANT EXTERNAL ACCESS ASSEMBLY TO SQLCLRTestLogin GO

|||

Hey Ikh,

Truly speaking, I m not finding words to thank you.

Your reply is exactly what I was looking for.

Thank you very …………………… much .

Actually the scenario at my end is that. In our project we have used an Assembly with UNSAFE Permissions. Now before installing the product we need to check whether the user has permissions to Access the UNSAFE ASSEMBLY.

Your reply is the exact answer to my question

Thanks again for spending time in replying to me.

Have a good time ahead!

How to know if a record is empty or not, without using QueryString?

Hi,

I wrote two queries to search in three tables mp_parent, mp_page and mp_parent

The first one is:

SELECT mp_page.PID, mp_page.PageID, mp_page.PageContent, mp_page.ParID, mp_page.ChiID, mp_parent.ParentName FROM mp_page INNER JOIN mp_parent ON mp_page.ParID = mp_parent.ParentID

The second one is:

SELECT mp_page.PID, mp_page.PageID, mp_page.PageContent, mp_page.ParID, mp_page.ChiID, mp_child.ChildName FROM mp_page INNER JOIN mp_child ON mp_page.ChiID = mp_child.ChildID

I used this way to display the records in a FormView

public HttpContext context = HttpContext.Current;
public void Page_Load(object sender, EventArgs e)
{
if (!Page.IsPostBack)
{
ViewState["srch"] = context.Items["srch"];
}
FormView1.DataSource = GetTable();
FormView1.DataBind();
}

private DataTable GetTable()
{
SqlConnection SqlCon = new SqlConnection("Data Source=AJ-166DCCD87;Initial Catalog=mp;Integrated Security=True;Pooling=False");
String SQL1 = "select mp_page.PID, mp_page.PageID, mp_page.PageContent, mp_page.ParID, mp_page.ChiID, mp_parent.ParentID, mp_parent.ParentName from mp_page INNER JOIN mp_parent ON mp_page.ParID = mp_parent.ParentID where PageContent like '%" + Convert.ToString(ViewState["srch"]) + "%'";
SqlDataAdapter Adptr = new SqlDataAdapter(SQL1, SqlCon);
SqlCommandBuilder CB = new SqlCommandBuilder(Adptr);
DataTable Dt = new DataTable();
Adptr.Fill(Dt);
return Dt;
SqlCon.Close();
}

the question is how can I check if one record is empty to witch to another query ??

Is it possible to know without using QueryString?

Thank you

If you are using Sql Server, or another db that supports stored procedures, you can move all of it to one of those, then use If Exists (Query1) Else (Query2). This would also protect you from SQL Injection, which, at the moment, since you are passing raw user input into your SQL, you are seriously at the risk of being the victim of.

If someone searched for "apples';Drop Table mp_page;--" with your current SQL, bang goes your table.

|||

Is there another way instead of using stored procedure ?

|||

Parameterized Queries will also work.

|||

Would you please give me any example to explain more.

Thank you,

|||

Yes. You can run the first query from your page, then examine the results in your code. If there are some, display them, otherwise run the second query. If you are going to do this, look at using parameters in your code. You should never accept user input directly into your SQL.

[Edit]

There is another way too. Run both queries at the same time - separated with a semicolon (;) and return them in a dataset. The first result will be in the first datatable, and the second in the second. If there are no rows in the first, display the second one.

How to know if a record exists

Hello,

I have a TextBox and an Insert Button, it works like this:

protected void Button1_Click(object sender, EventArgs e)
 {
 String strConn=SqlDataSource1.ConnectionString;
 SqlCommand cmd =new SqlCommand("INSERT INTO My_Table_1 (Column_1) VALUES ("+TextBox1.Text+")",new SqlConnection(strConn));
 cmd.Connection.Open();
 cmd.ExecuteNonQuery();
 cmd.Connection.Close();
 }

But,what code i must write for checking, if the value i am trying to insert already exists ??

I mean something like this:

if (TextBox1.Text does not Exists on any Record in My_Table_1) then
Insert it
else
Show Message : "Already exists a record with this value"

Thank you SO MUCH, guys,

Carlos.you can execute "select count(*) from My_Table_1 whereColumn_1='"+TextBox1.Text+"'" before insert and get value of the firstcolumn, then use if to determine whether it is bigger than 0|||

you could write a stored procedure and pass the insert parameters to it. In the stored proc you could do a check as follows:

IF NOT EXISTS(SELECT * FROM <table> WHERE <Condition>)

BEGIN

INSERT INTO <table> (<columns>) VALUES (<Values>)

END

If you want to return an appropriate message back to the front end you could include a return value as an OUTPUT parameter and return a code appropriately.

|||Hello Tony and Ndinakar:

thanks for the replies ;)

I will try first Tony's method...

BUT, how can i get the value of the first column ?

Please, can you write some code? i am REALLY new on this, and i can't find the answer :(

Thank you so much,

Carlos.|||code sample for you:)

//open connection
SqlConnection m_conn=new SqlConnection(ConnectionString);
m_conn.Open();
//run sql
sqlstring="select count(*) from My_Table_1 whereColumn_1='"+TextBox1.Text+"'" ";
SqlCommand sqlcmd=new SqlCommand(sqlstring,m_Conn);
SqlDataReader sdr=sqlcmd.ExecuteReader();
sdr.Read();
int rowcount=(int)sdr[0]; //here, you get what you need!
sdr.Close();
//determine whether record exist
if(rowcount==0)
{
//insert a new record
}
//close you connection
m_conn.Close();|||With all due respect... The second solution will give you better performance. It is essentiall the same as the first, except the exists contraint tells the query to stop executing after it finds a single match, whereas the count(*) method continues to aggregate the entire selection.|||Yes, you will be making 2 trips to insert a record which is unnecessary.|||Thank you guys for all your replies.

Tony, your code works 100% for what i am looking for, thank you ;)

In a few days i will study stored procedures, and i will try the second solution, and i will post my results here.

Thanks again,

Carlos.

How to know if a job is running...

Howdy folks... just a quick question. Is there a system table that holds a variable that states if a job is currently running or not? Something like a status column for jobs. If so, where is it? and if not, any suggestions aside from setting a flag in a table when it runs and stops?
TIAha! Found my own answer... sp_help_job

how to know if a DEFAULT exists or not

For a script that should update some table-definitions, I would like to know
how i can now if a DEFAULT exists for a given row in a table.
for exemple:
ALTER TABLE MyTable ADD
CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
This adds the Default NEWID() to the row MyRow in the table MyTable.
But when i run this query a second time, i get a warning ("Column already
has a DEFAULT bound to it.") that it already exists. So I would like my
query check first if it is there or not, and if not add the Default.
How do i do this? I can't find anything on google :-/
Thanks,
Pieter
IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
Constraint_Name like 'DF_MyTable_MyRow'
ALTER TABLE MyTable ADD
CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"DraguVaso" <pietercoucke@.hotmail.com> schrieb im Newsbeitrag
news:Oct$xeKVFHA.2520@.TK2MSFTNGP09.phx.gbl...
> For a script that should update some table-definitions, I would like to
> know
> how i can now if a DEFAULT exists for a given row in a table.
> for exemple:
IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
Constraint_Name like 'DF_MyTable_MyRow'
ALTER TABLE MyTable ADD
CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
> This adds the Default NEWID() to the row MyRow in the table MyTable.
> But when i run this query a second time, i get a warning ("Column already
> has a DEFAULT bound to it.") that it already exists. So I would like my
> query check first if it is there or not, and if not add the Default.
> How do i do this? I can't find anything on google :-/
> Thanks,
> Pieter
>
|||hi,
Unfortunately it works only for CHECK, UNIQUE, PRIMARY KEY, or FOREIGN KEY,
and not for DEFAULT :-(
any other idea?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ew4NznKVFHA.3540@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
> Constraint_Name like 'DF_MyTable_MyRow'
> ALTER TABLE MyTable ADD
> CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "DraguVaso" <pietercoucke@.hotmail.com> schrieb im Newsbeitrag
> news:Oct$xeKVFHA.2520@.TK2MSFTNGP09.phx.gbl...
> IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
> Constraint_Name like 'DF_MyTable_MyRow'
> ALTER TABLE MyTable ADD
> CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
already
>
|||create table t(c1 int default 1)
GO
select *
from sysobjects so inner join sysconstraints sc on so.id = sc.id
where so.id = object_id('t')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:Oct$xeKVFHA.2520@.TK2MSFTNGP09.phx.gbl...
> For a script that should update some table-definitions, I would like to know
> how i can now if a DEFAULT exists for a given row in a table.
> for exemple:
> ALTER TABLE MyTable ADD
> CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
> This adds the Default NEWID() to the row MyRow in the table MyTable.
> But when i run this query a second time, i get a warning ("Column already
> has a DEFAULT bound to it.") that it already exists. So I would like my
> query check first if it is there or not, and if not add the Default.
> How do i do this? I can't find anything on google :-/
> Thanks,
> Pieter
>
|||Your are right, you can try that from Dejan Serka:
select
db_name() as CONSTRAINT_CATALOG
,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
,c_obj.name as CONSTRAINT_NAME
,com.text as DEFAULT_CLAUSE
from
sysobjects c_obj
,syscomments com
where
c_obj.uid = user_id()
and c_obj.id = com.id
and c_obj.xtype = 'D'
Found under
http://sql.reproms.si/data/podatki/d...st raints.sql
to change to standard beaviour of the Information Views,
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"DraguVaso" <pietercoucke@.hotmail.com> schrieb im Newsbeitrag
news:OluNH3KVFHA.1452@.TK2MSFTNGP14.phx.gbl...
> hi,
> Unfortunately it works only for CHECK, UNIQUE, PRIMARY KEY, or FOREIGN
> KEY,
> and not for DEFAULT :-(
> any other idea?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:ew4NznKVFHA.3540@.TK2MSFTNGP15.phx.gbl...
> already
>
|||Try,
declare @.sql nvarchar(4000)
declare @.cnstname sysname
select
@.cnstname = [name]
from
sysobjects as so
where
xtype = 'D'
and parent_obj = object_id('dbo.t')
and col_name(parent_obj, info) = 'colB'
if @.cnstname is not null
print @.cnstname
else
print 'no default for column [colB] in table [t].'
go
AMB
"DraguVaso" wrote:

> For a script that should update some table-definitions, I would like to know
> how i can now if a DEFAULT exists for a given row in a table.
> for exemple:
> ALTER TABLE MyTable ADD
> CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
> This adds the Default NEWID() to the row MyRow in the table MyTable.
> But when i run this query a second time, i get a warning ("Column already
> has a DEFAULT bound to it.") that it already exists. So I would like my
> query check first if it is there or not, and if not add the Default.
> How do i do this? I can't find anything on google :-/
> Thanks,
> Pieter
>
>
|||Ok thanks! Works fine!
The solution of Alejandro works fine too! thanks a lot guys!
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23BWTRFLVFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Your are right, you can try that from Dejan Serka:
> select
> db_name() as CONSTRAINT_CATALOG
> ,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
> ,c_obj.name as CONSTRAINT_NAME
> ,com.text as DEFAULT_CLAUSE
> from
> sysobjects c_obj
> ,syscomments com
> where
> c_obj.uid = user_id()
> and c_obj.id = com.id
> and c_obj.xtype = 'D'
> Found under
>
http://sql.reproms.si/data/podatki/d...st raints.sql[vbcol=seagreen]
> to change to standard beaviour of the Information Views,
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "DraguVaso" <pietercoucke@.hotmail.com> schrieb im Newsbeitrag
> news:OluNH3KVFHA.1452@.TK2MSFTNGP14.phx.gbl...
to[vbcol=seagreen]
my
>

how to know if a DEFAULT exists or not

For a script that should update some table-definitions, I would like to know
how i can now if a DEFAULT exists for a given row in a table.
for exemple:
ALTER TABLE MyTable ADD
CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
This adds the Default NEWID() to the row MyRow in the table MyTable.
But when i run this query a second time, i get a warning ("Column already
has a DEFAULT bound to it.") that it already exists. So I would like my
query check first if it is there or not, and if not add the Default.
How do i do this? I can't find anything on google :-/
Thanks,
PieterIF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
Constraint_Name like 'DF_MyTable_MyRow'
ALTER TABLE MyTable ADD
CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"DraguVaso" <pietercoucke@.hotmail.com> schrieb im Newsbeitrag
news:Oct$xeKVFHA.2520@.TK2MSFTNGP09.phx.gbl...
> For a script that should update some table-definitions, I would like to
> know
> how i can now if a DEFAULT exists for a given row in a table.
> for exemple:
IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
Constraint_Name like 'DF_MyTable_MyRow'
ALTER TABLE MyTable ADD
CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
> This adds the Default NEWID() to the row MyRow in the table MyTable.
> But when i run this query a second time, i get a warning ("Column already
> has a DEFAULT bound to it.") that it already exists. So I would like my
> query check first if it is there or not, and if not add the Default.
> How do i do this? I can't find anything on google :-/
> Thanks,
> Pieter
>|||hi,
Unfortunately it works only for CHECK, UNIQUE, PRIMARY KEY, or FOREIGN KEY,
and not for DEFAULT :-(
any other idea?
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:ew4NznKVFHA.3540@.TK2MSFTNGP15.phx.gbl...
> IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
> Constraint_Name like 'DF_MyTable_MyRow'
> ALTER TABLE MyTable ADD
> CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "DraguVaso" <pietercoucke@.hotmail.com> schrieb im Newsbeitrag
> news:Oct$xeKVFHA.2520@.TK2MSFTNGP09.phx.gbl...
> IF NOT EXISTS(SELECT * from INFORMATION_SCHEMA.TABLE_CONSTRAINTS WHERE
> Constraint_Name like 'DF_MyTable_MyRow'
> ALTER TABLE MyTable ADD
> CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
already[vbcol=seagreen]
>|||create table t(c1 int default 1)
GO
select *
from sysobjects so inner join sysconstraints sc on so.id = sc.id
where so.id = object_id('t')
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DraguVaso" <pietercoucke@.hotmail.com> wrote in message
news:Oct$xeKVFHA.2520@.TK2MSFTNGP09.phx.gbl...
> For a script that should update some table-definitions, I would like to kn
ow
> how i can now if a DEFAULT exists for a given row in a table.
> for exemple:
> ALTER TABLE MyTable ADD
> CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
> This adds the Default NEWID() to the row MyRow in the table MyTable.
> But when i run this query a second time, i get a warning ("Column already
> has a DEFAULT bound to it.") that it already exists. So I would like my
> query check first if it is there or not, and if not add the Default.
> How do i do this? I can't find anything on google :-/
> Thanks,
> Pieter
>|||Your are right, you can try that from Dejan Serka:
select
db_name() as CONSTRAINT_CATALOG
,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
,c_obj.name as CONSTRAINT_NAME
,com.text as DEFAULT_CLAUSE
from
sysobjects c_obj
,syscomments com
where
c_obj.uid = user_id()
and c_obj.id = com.id
and c_obj.xtype = 'D'
Found under
http://sql.reproms.si/data/podatki/...Constraints.sql
to change to standard beaviour of the Information Views,
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"DraguVaso" <pietercoucke@.hotmail.com> schrieb im Newsbeitrag
news:OluNH3KVFHA.1452@.TK2MSFTNGP14.phx.gbl...
> hi,
> Unfortunately it works only for CHECK, UNIQUE, PRIMARY KEY, or FOREIGN
> KEY,
> and not for DEFAULT :-(
> any other idea?
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in
> message news:ew4NznKVFHA.3540@.TK2MSFTNGP15.phx.gbl...
> already
>|||Try,
declare @.sql nvarchar(4000)
declare @.cnstname sysname
select
@.cnstname = [name]
from
sysobjects as so
where
xtype = 'D'
and parent_obj = object_id('dbo.t')
and col_name(parent_obj, info) = 'colB'
if @.cnstname is not null
print @.cnstname
else
print 'no default for column [colB] in table [t].'
go
AMB
"DraguVaso" wrote:

> For a script that should update some table-definitions, I would like to kn
ow
> how i can now if a DEFAULT exists for a given row in a table.
> for exemple:
> ALTER TABLE MyTable ADD
> CONSTRAINT [DF_MyTable_MyRow] DEFAULT (newid()) FOR MyRow'
> This adds the Default NEWID() to the row MyRow in the table MyTable.
> But when i run this query a second time, i get a warning ("Column already
> has a DEFAULT bound to it.") that it already exists. So I would like my
> query check first if it is there or not, and if not add the Default.
> How do i do this? I can't find anything on google :-/
> Thanks,
> Pieter
>
>|||Ok thanks! Works fine!
The solution of Alejandro works fine too! thanks a lot guys!
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:%23BWTRFLVFHA.3152@.TK2MSFTNGP12.phx.gbl...
> Your are right, you can try that from Dejan Serka:
> select
> db_name() as CONSTRAINT_CATALOG
> ,user_name(c_obj.uid) as CONSTRAINT_SCHEMA
> ,c_obj.name as CONSTRAINT_NAME
> ,com.text as DEFAULT_CLAUSE
> from
> sysobjects c_obj
> ,syscomments com
> where
> c_obj.uid = user_id()
> and c_obj.id = com.id
> and c_obj.xtype = 'D'
> Found under
>
http://sql.reproms.si/data/podatki/...nts.
sql
> to change to standard beaviour of the Information Views,
>
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "DraguVaso" <pietercoucke@.hotmail.com> schrieb im Newsbeitrag
> news:OluNH3KVFHA.1452@.TK2MSFTNGP14.phx.gbl...
to[vbcol=seagreen]
my[vbcol=seagreen]
>