Prevent Insert && Update If Field Archived = True

Apr 15, 2008

Hi,

I've got a table containing calculated values, so i created a field named "archived" (bit datatype) on this table, to prevent the values to be updated if this field is set to true.

Is it possible to created a constraint, to prevent the row to be updated if ARCHIVED=true ? How can i do it ?

View 7 Replies


ADVERTISEMENT

Insert True/False Into Boolean Field

Jun 12, 2008

When I enter a a true or false value into a boolean field, what is the proper way to enter it? I'm using:

INSERT INTO tblTable (IsSomething)
VALUES (False)

I've heard that a better way is to use 1 and 0, or something.

View 1 Replies View Related

SQL Server 2008 :: Update Null Enabled Field Without Interfering With Rest Of INSERT / UPDATE

Apr 16, 2015

If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?

EXAMPLE:

CREATE TABLE dbo.MYTABLE(
ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL,
FirstName VARCHAR(50) NULL,
LastName VARCHAR(50) NULL,

[Code] ....

If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
VALUES('John','Smith',NULL)

INSERT INTO dbo.MYTABLE( FirstName, LastName)
VALUES('John','Smith')

INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded)
SELECT FirstName, LastName, NULL
FROM MYOTHERTABLE

View 9 Replies View Related

Can I Insert/Update Large Text Field To Database Without Bulk Insert?

Nov 14, 2007

I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind.  I've tried using the new .write() method in my update statement, but it cuts off the text after a while.  Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.

View 6 Replies View Related

Update/Insert Date Field, Which Did Not Import From Access

Jul 12, 2007

First off, it has been a few years since I have done extensive work with SQL and that was using Oracle. But I am trying to develop a simple asset database for work, as we have nothing in place. I started out with Access, and decided to move to SQL express for many reasons.

What I have now is that I imported my data from my access 97 database to Excel, only my AssetTable did not import dates, I assume because Access and Sql Express handle dates differently... so a the time I just ignored that column.

Is it possible to insert the dates into the now populated SQL Express database AssetTbl where the AssetID's match? Here is what I have.

Sql Express Database Name: BAMS
Table Name: AssetTbl
fields: AssetID, SerialNum ...(many other fields)... DateAcq <- currently Null

Excel file: AssetDateAcq.xls
fields: AssetID, DateAcq (in format 07/12/2007)

To me it sounds like I need to do a short script/program to loop through the file read an AssetID from the excel file, and the DateAqcuired and then have it do an update on the DateAcq field, but it has been so long since I've done any work with SQL that I am finding there is a lot of "Dust" to blow off, and I don't know if I'm heading down the right track... or completely off course.

Thank you.

View 9 Replies View Related

Transact SQL :: How To Update A Field And Insert Increment Count

May 28, 2015

I want to update a field and in this field insert a increment count, for example:

When I make, "Select * from Users order by User" displays:

User1  |  NULL
User1  |  NULL
User1  |  NULL
User2  |  NULL
User2  |  NULL

and I want to do this:

User1  |  1
User1  |  2
User1  |  3
User2  |  1
User2  |  2

how to do this?

View 7 Replies View Related

Trigger To Count Words In Field On Update,insert

Aug 28, 2007

I want to create a trigger that, when a field is updated or a record is inserted, counts the number of words in "field1", inserting this numeric value into another field in the same table. What would be the most efficient, fastest method for doing this?

View 15 Replies View Related

Trying To Insert Checkbox True/false Into Db

Aug 15, 2005

Hi there, I've tried googling this (and looking at the many questions on the forum :) but I've not managed to find a decent tutorial / guide that describes a method for using checkboxs to insert true/false flags in a MS SQL db.  The db field i'm setting has type set to "bit", is this correct?  And secondly (its been a long day!) I just cant figure out the code to assign the bit 1 or 0 / true or false. This is what I've got so far but it's not working........Function InsertProduct(ByVal prod_code As String, ByVal prod_name As String, ByVal prod_desc As String, ByVal prod_size As String, ByVal prod_price As String, ByVal prod_category As String, ByVal aspnet As Boolean) As Integer             Dim connectionString As String = "server='server'; user id='sa'; password='msde'; Database='dbLD'"             Dim sqlConnection As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(connectionString)                 Dim queryString As String = "INSERT INTO [tbl_LdAllProduct] ([prod_code], [prod_name], [prod_desc], [prod_size], [prod_price], [prod_category],[aspnet]) VALUES (@prod_code, @prod_name, @prod_desc, @prod_size, @prod_price, @prod_category, @aspnet)"             Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(queryString, sqlConnection)                 sqlCommand.Parameters.Add("@prod_code", System.Data.SqlDbType.VarChar).Value = prod_code             sqlCommand.Parameters.Add("@prod_name", System.Data.SqlDbType.VarChar).Value = prod_name             sqlCommand.Parameters.Add("@prod_desc", System.Data.SqlDbType.VarChar).Value = prod_desc             sqlCommand.Parameters.Add("@prod_size", System.Data.SqlDbType.VarChar).Value = prod_size             sqlCommand.Parameters.Add("@prod_price", System.Data.SqlDbType.VarChar).Value = prod_price             sqlCommand.Parameters.Add("@prod_category", System.Data.SqlDbType.VarChar).Value = prod_category                 If chkAspnet.Checked = True Then                sqlCommand.Parameters.Add("@aspnet","1")             Else                sqlCommand.Parameters.Add("@aspnet","0")             End If                     Dim rowsAffected As Integer = 0             sqlConnection.Open             Try                 rowsAffected = sqlCommand.ExecuteNonQuery             Finally                 sqlConnection.Close             End Try             Return rowsAffected         End Function             Sub SubmitBtn_Click(sender As Object, e As EventArgs)            If Page.IsValid then                InsertProduct(txtCode.Text, txtName.Text, txtDesc.Text, ddSize.SelectedItem.value, ddPrice.SelectedItem.value, ddCategory.SelectedItem.value, aspnet.value)                Response.Redirect("ListAllProducts.aspx")            End If    End SubAny help would be appreciated or links to tutorials.ThanksBen

View 2 Replies View Related

Analysis :: Cube Dimension With Bool Filter Shows (blank) And True Instead Of False And True?

Jul 31, 2015

I have a cube that has a Dimension set up with several values some of which are bools. While Browsing in Excel or SSMS, two new values, when used as a filter shows (All) (Blank) and (True) for selections instead of (All) (True) and (False). 

View 2 Replies View Related

Update Trigger: COLUMNS_UPDATED Evaluates To True For All String Fields

Jul 14, 2006

I have a trigger that writes changes to an audit table. In the case of Updates, I only want to write out the fields that have actually changed. My code for COLUMNS_UPDATED evaluates to true for all varchar fields, even when they haven't changed. All other scenarios appear to be working correctly.



WHILE @field < @maxfield
BEGIN
SELECT @field = min(ORDINAL_POSITION) FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName AND ORDINAL_POSITION > @field
SELECT @bit = (@field - 1 )% 8 + 1
SELECT @bit = power(2,@bit - 1)
SELECT @char = ((@field - 1) / 8) + 1

IF ((@TriggerType = 'I') OR (@TriggerType = 'D') OR (@TriggerType = 'U' AND substring (COLUMNS_UPDATED(),@char, 1) & @bit > 0))
BEGIN..........

END

END

Ideas? Thanks!

View 1 Replies View Related

Rs.CreateReport() With Overwrite Set To True Fails To Update Report Description

Jan 3, 2007

When calling the RS SOAP API's CreateReport method, it doesn't seem like the report description is updated, unless I first delete the report and then recreate it.

Here's what my call looks like -
Warning[] warnings = rs.CreateReport(reportName, "/" + folder, true, buffer, null);

Is this a known issue?

View 9 Replies View Related

Prevent Duplication On UPDATE

Apr 15, 2007

Hello
I noticed a spelling mistake in the data in a column of several tables, I used the following syntax to alter the spelling:
UPDATE [dbo].[Prod_Cat]     SET  [ProdName]=N'merseyside'    WHERE ProdName = 'mmserseyside'
The above code correctly updated the spelling error, but it also inserted a new row with the corrected data. 
So I found myself with two Identical rows containing the corrected information. I had to manually delete the extra row. Because if I had put in a DELETE statement, I would have then lost both rows.
What do I need to do to prevent this happening next time.  As I find that I need to update the names of some products, but I don't want to duplicate them.
Thanks
Lynn

View 3 Replies View Related

Trigger To Prevent Update If No Changes?

Jul 10, 2013

Is it possible to create a trigger to prevent update of a row if no signifiant colums are updated

Example : I need to allow the update only if colums a,b or c have changes ?

how it should works to keep or discard the update according to condition ?

View 1 Replies View Related

Prevent DELETE And/or UPDATE

Jul 23, 2005

Hi all!Are there any other way than using rights or Triggers to prevent aDELETE or an UPDATE on a specific column.The "problem" with rights is that they dont apply to all DB-usersThe "problem" with triggers is that they generate lots of extraSQL-codeI would like a solution something like below. If there are anyprimitives like this or other more neat solutions I would be glad toknowCREATE TABLE some_table NO DELETE/* ^^^^^^^^^*/(some_column SOME_TYPE NO UPDATE/* ^^^^^^^^^*/)For clarity, here is a trigger that currently solves the problemCREATE TRIGGER check_updateable_columns ON some_tableFOR UPDATEASIF UPDATE(some_column)RAISERROR(...)GOorCREATE TRIGGER delete_not_allowed ON some_tableINSTEAD OF DELETEASRAISERROR(...)GO

View 1 Replies View Related

Prevent Duplicate Insert

Oct 18, 2007

How can I prevent duplicate inserts or entries to a table?
Thank you.
Note: I am using SQL Server and coding ASP.net pages in VB.

View 7 Replies View Related

Integration Services :: UPDATE Data Overwritten - How To Prevent

Nov 16, 2015

I'm trying to find a way to maintain the data that was UPDATEd in a table from being overwritten.

I used UPDATE in a table to include missing data.  However, since I do not have access to the original table from where the data is derived, when I run my query, it knocks out the data in my table that was updated, because it is linked to the orginal table that has the blank fields. A few users do not have an employee email, and this has to be updated with a personal email account for now.  

My update works fine, but is there a way I can use the statement in my query so that it updates each time it is run to maintain the user email address? This will solve the problem of not having to change the original table that I do not have access to.

if exists (select * test..dbo.Employee)
truncate TABLE
 test.. dbo.Employee
insert into test.. dbo.Employee (EmployeeNumber  
    , EmplUsername  

[Code] ....

View 5 Replies View Related

How Do I Prevent An Insert Into Statement To Increase Tempdb Data Files So Much

May 6, 2008

I'm running this procedure which insert into table_name(id, name.....) select id, name.... from table_name. For some reason the tempdb data file grow up to 200GB. The tempdb is set to expand unrestricted by 10%. How can I prevent that from hapening? Thanks.

View 5 Replies View Related

SQL Server 2008 :: How To Locate Archived Log Files

Jun 18, 2015

SQL SERVER 2008R2

The number of files to retain for SQL Server Logs is set to 99. When I expand the SQL Server Logs node in SQL Server Mgt Studio it shows the current log file through Archive#49. The oldest archive file is dated 2015/05/26. If I select that archive in SQL Server Mgt Studio it shows me the details and entries of that archive file. Yet when I go to the directory on SQL Server for the log files there are only the 5 most recent files. I have searched for '.trc' files on the entire drive and have found no other files.

How can SQL Server Mgt Studio show archive files that have no corresponding archive file in the directory that is supposed to contain the log files?

View 1 Replies View Related

SQL 2012 :: UK Date In Text Field As True Date

Jul 14, 2014

I have a date held in a varchar field in a temporary sql table and I want to convert it into a sql date and it doesn't work. I can replicate this as below -

So I run

select
cast ('14/02/2014' as date)

and I get a conversion failed error.

View 2 Replies View Related

Can MsSQL Data File Be Archived And Made Smaller ?

Dec 6, 2006

Hi... We have application that connected to MSSQL 2000 database. The database file is getting bigger and bigger over the years. Recently the performance of the database and application is getting slower and slower, my senior oracle DBA told me that I should archive the MSSQL 2000 data file and export the old record to the archive DB. So that the number of record will be less and it will be faster.

I would like to know whether MSSQL 2000 support archiving of the database file ? If yes, what is the way to do it ? I could not find it in Enterprise manager option at all.

View 2 Replies View Related

Update Field With Trigger Only If A Specific Field Is Updated

Nov 11, 2013

I want to update a field with a trigger only if a specific field is updated.

When I try the code below, it updates the field when any field in the record is updated. Is there a way to only make look at picked_dt?

ALTER TRIGGER [dbo].[UpdatePickedDate]
on [dbo].[oeordlin_sql]
after update
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from

[Code] .....

View 4 Replies View Related

Fail To Update Field With A Field Uniqueidentifier

Mar 30, 2004

Hi all,
I have a problem about a query to update a table

UPDATE Email SET EmailDT='31 Mar 2004' WHERE Idx={BDF51DBD-9E4F-4990-A751-5B25D071E288}

where Idx field is a uniqueidentifier type and EmailDT is datetime type. I found that when this query calling by a VB app. then it have error "[Microsoft][ODBC SQL Server Driver]Syntax error or access violation" and i have tried again in Query Analyzer, same error also occur, the MS SQL server is version 7. Please help. thanks.

View 2 Replies View Related

Update SQL Field With Stripped Data From Other Field

May 12, 2006

Not a SQL guy but can do enough to be dangerous :)Trying to update a record. We have records that have a field with datasurrounded by some comment text such as *** Previous Public Solution*** Start and *** Previous Public Solution *** End . What I am tryingto do is write a SQL statement that will:Check that field C100 = TICKET0001 (to test with one record beforerunning on whole db)Check that field C101 is = ClosedCheck that field C102 is nullCopy field C103 data to field C102 and strip out any words such as ***Previous Public Solution *** Start and *** Previous Public Solution*** endThanks for any help!Kevin

View 1 Replies View Related

How To Update Another Field From Combo Field

Oct 31, 2012

In Access, you have a combo with column designations for example me.combofield.column(x) and you can update another field with those column(x) values.

How do you do it in MS SQL?

I didn't mean "from a 'combo' field'" in SQL(!) I just want to reproduce the equivalent of an Access combo box.

View 14 Replies View Related

T-SQL (SS2K8) :: Insert / Update Triggers When Insert Run Via Script

Oct 23, 2014

I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.

This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.

View 1 Replies View Related

How Do I Update A Field Only If Update Value Is Not Null?

Oct 25, 2006

Hi. I'm not  such an expert in sql and I wanted to update a table's fields only if the value that im going to update it with is not null. How do I do that?

What I mean is something like:

---------------------------------------------------

Update database.dbo.table set

if(@newvalue !=null)

     afield = @newvalue;

-------------------------------------------------

 

Really need your help on this. Thanks.

View 9 Replies View Related

Trigger To Update A Table On Insert Or Update

Feb 15, 2008



Hello

I've to write an trigger for the following action

When a entry is done in the table Adoscat79 having in the index field Statut_tiers the valeur 1 and a date in data_cloture for a customer xyz

all the entries in the same table where the no_tiers is the same as the one entered (many entriers) should have those both field updated

statut_tiers to 1
and date_cloture to the same date as entered

the same action has to be done when an update is done and the valeur is set to 1 for the statut_tiers and a date entered in the field date_clture

thank you for your help
I've never done a trigger before

View 14 Replies View Related

Single Complex INSERT Or INSERT Plus UPDATE

Jul 23, 2005

Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.

View 4 Replies View Related

Can I Roll Back Certain Query(insert/update) Execution In One Page If Query (insert/update) In Other Page Execution Fails In Asp.net

Mar 1, 2007

Can I roll back certain query(insert/update) execution in one page if  query (insert/update) in other page  execution fails in asp.net.( I am using sqlserver 2000 as back end)
 scenario
In a webpage1, I have insert query  into master table and Page2 I have insert query to store data in sub table.
 I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance

View 2 Replies View Related

Update A Field

Jan 4, 2006

Hello,
I have a field in a table that I have to update to the number of the week.
Information i use is from another field, which is text in the format like :


01022005
02142005
07082005
11222005
...
12022005

mmddyyyy.

How to write an update to update empty field with a number of the week based on the value from another field.



Thank you very much.

View 3 Replies View Related

Trying To Update In A Field That Contains Xml

Nov 11, 2013

Trying to run this SQL script

update Promotions

set PromotionDiscountData = '<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionRuleBase xsi:type="StartDatePromotionRule"><StartDate>2013-11-11T00:00:00</StartDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationDatePromotionRule"><ExpirationDate>2014-01-12T00:00:00</ExpirationDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationNumberOfUsesPerCustomerPromotionRule">

[code]...

but getting this error

Msg 402, Level 16, State 1, Line 1

The data types xml and varchar are incompatible in the equal to operator.Basically within each cell I am trying to change the StartDate only

View 5 Replies View Related

Get The Field Value After INSERT

Jul 23, 2005

Hi,Problem:I need to get the value of auto-incremented field from just insertedrecordIn Oracle this is INSERT .. RETURNING command.In SQL Server there are @@IDENTITY, IDENT_CURRENT, SCOPE_IDENTITY- @@IDENTITY returns the value from the very LAST insert on any tableinvolving in the insert process ( including triggers ) ,so this value may have nothing to do with my table- IDENT_CURRENT returns the last identity value generated for aspecific table in any session and any scope,so this value may come not from my session- SCOPE_IDENTITY returns the last identity value generated for anytable in the current session and the current scope , but from the veryLAST insert command ( that may be some INSERT in the audit tables)so it may have nothing to do with my tableQuestion :- Is there any trusted way I can get the value of auto-incrementedfieldin my table and in the scope of my session?Thanks, Eugene

View 2 Replies View Related

Update Field Without Retreive

Jan 24, 2007

if a db field "mynum" of type int contains for example the number 543.Is there a way I can update this field without first selecting the value?so for example set the the current value to +1
 

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved