Trigger And Identity Column : What's Wrong ?

Mar 22, 2008

Hi,

I have to tables : Rank and Item
Rank contains a FK named R_I_id which references Item's primary key named I_id, which is also an identity column.

I want the field R_I_id of table Rank to be updated with the value of Item's I_id when a new Item is inserted. So I wrote the following trigger :

CREATE TRIGGER [dbo].[trg_oninsertitem]
ON [dbo].[Item]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON
SET IMPLICIT_TRANSACTIONS OFF
DECLARE @I_id INT
SELECT @I_id = SCOPE_IDENTITY()

BEGIN TRANSACTION
INSERT Rank(R_I_id) VALUES(@I_id)
IF (@@ERROR <> 0) BEGIN
PRINT 'error in trigger trg_oninsertitem'
ROLLBACK TRANSACTION
END
COMMIT
END

But when I insert a value in Item, I got the following message :

Msg 515, Level 16, State 2, Procedure trg_oninsertitem, Line 17
Cannot insert the value NULL into column 'R_T_id', table 'base.dbo.Rank'; column does not allow nulls. INSERT fails.

and I don't understand, because my trigger is fired after the value is inserted in table Item, so the field I_id should contain a new identity value, not NULL

my DBMS is SQL Server 2000

thanks in advance for your help

View 17 Replies


ADVERTISEMENT

Trigger Update Identity Column

Feb 5, 2001

hi,

i got a little problem. when i insert values into a table a trigger is launched wich updates something in the table too.

CREATE TRIGGER T1 ON Tbl
FOR INSERT AS
BEGIN
IF (UPPER(USER) <> 'CONVERSIE')
BEGIN
UPDATE Tbl SET
Datum = GetDate(),
User = USER,
WHERE Tbl.ID= (SELECT ID FROM inserted)
END
END

the error i receive is this: 'can not update identity column id' (id being pk).

does anyone know how to deal with this?

tnx,
erik.

View 1 Replies View Related

Reseed The Identity Column With Trigger.

Oct 26, 2005

I need to populate a temporary holding table from a trigger. The contents of this table will be added to another table when the Trigger has completed processing. The id column of the temporary table will be added to the max value in the final table to create the record identifier. I used an identity field in the temporary table to accomplish this. My initial idea was to create a temp table in the code for the trigger then drop the temp table when each recursion is complete. However I got "cannot use Create table in Trigger". Then I tried to make temp table in stored proc called by trigger. Same message. Then made permanent "temp" table, and tried to Trucate table after each use. I got "Cannot use Trucate Table in Trigger". Finally I used delete from and DBCC Checkident to reseed identity. This worked in Query analyzer. I worked in VB project using ADO. However, I after deployment I was informed that VB project using RDS to make and update ADO recordsets was failing to run the trigger. After troubleshooting I was alerted that DBCC CheckIdent can only be run by table owner. Business rules prevent this. Therefore I would like to know if anyone knows any other way to reseed an identity, before I abandon this approach and start over.

Thanks for trying.

View 9 Replies View Related

Transact SQL :: Trigger On Identity Column

Nov 21, 2015

I am new in sql server. I am using TSQL2012 database. I have added a new column in processedOrderCount in Hr.Employees table. And created a trigger on Sales.Orders table that whenever orderId is updated it automatically updated processedOrderCount in Hr.Employees. There is a problem that the orderId is the identity column I can't update it. How can we work with identity column? the code of my trigger is:

If Object_id ('Sales.trig_Calculate_OrderProcessed', 'tr') is not null
Drop Trigger Sales.trig_Calculate_OrderProcessed
go
Create Trigger Sales.trig_Calculate_OrderProcessed
ON Sales.Orders

[Code] ....

View 2 Replies View Related

Problem In Using Sqlbulkcopy To Insert Data From Datatable(no Identity Column) Into Sql Server Table Having Identity Column

Jun 19, 2008

Hi,
I am having problem in bulk update of a sql server table haning identity column from a datatable( has no identity column) using sqlbulkcopy. I tried several approaches, but it does not show any error nor is the table getting updated. But the identity value seems to getting increased every time.
thanks.
varun

View 6 Replies View Related

Procedure Returns Wrong @@Identity

Jan 15, 2004

Hi,

I have a procedure that inserts a record to a table and return the @@Identity as an output parameter.

This table has a trigger that is triggered when a record is inserted and add a new record to other table.

The problem is that the @@Identity returned is totally wrong.

Any ideas?

Thanx in advance.

View 3 Replies View Related

Transact SQL :: Alter Non Identity Column To Identity Column

Aug 12, 2009

when i alter non identity column to identity column using this Query alter table testid alter column test int identity(1,1) then i got this error message Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword 'identity'.

View 2 Replies View Related

What's Wrong In This Trigger

Mar 29, 2007

ALTER TRIGGER Trigger1
ON countries
FOR UPDATE AS
begin
insert into country1
select * from updated
end
Above trigger is been saved in sql 2005 query window
But if i execute any update stmt as
" update countries set cname='malathi' where cid=3 "
it shows following error
Invalid object name 'updated'.

Malathi Rao

View 5 Replies View Related

Trigger On Delete - What Am I Doing Wrong???

Oct 31, 2005

i have the following trigger ...anytime the user table is inserted or updated it writes to the UserProfileLog...which is what I want...but I also want it to write into UserProfileLog when the record from Users is deleted

here is my trigger...what am I doing wrong???

CREATE trigger trg_UserProfiles
on dbo.Users
for insert, update, delete
as
insert into UserProfileLog(UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy)
select UserID, UserAccess, UserFirstName, UserLastName, UserName, UserEmail, UserStreet, UserCity, UserState, UserZip, UserHomePhone, UserWorkPhone, UserCellPhone, UserPager, EditedBy
from inserted

View 2 Replies View Related

T-SQL (SS2K8) :: How To Update Identity Column With Identity Value

Jan 25, 2015

I have table of three column first column is an ID column. However at creation of the table i have not set this column to auto increment. Then i have copied 50 rows in another table to this table then set the ID column values to zero.

Now I have changed the ID column to auto increment seed=1 increment=1 but the problem is i couldn't figure out how to update this ID column with zero value set to each row with this auto increment values so the ID column would have values from 1-50. Is there a away to do this?

View 6 Replies View Related

Identity...I Need To Get The Last (or Highest Number In Identity Column)...

Sep 19, 2005

Ok,I just need to know how to get the last record inserted by the highestIDENTITY number. Even if the computer was rebooted and it was twoweeks ago. (Does not have to do with the session).Any help is appreciated.Thanks,Trint

View 2 Replies View Related

How To Use Identity On Non-identity Column (with Concurrence)

Aug 1, 2014

I'm working with a third-party database (SQL Server 2005) and the problem here is the following:

- There are a bunch of ETL processes that needs to insert rows on a table (let's call this table T) and at the same time, an ERP (owner of T) is up and running (reading, updating and inserting on T).

- The PK of T is an Integer.

Today all ETL processes uses (select max(ID) + 1 from T) to insert new rows, so just picture the scenario. It is a mess! Everyday they get duplicate key error when 2 or more concurrent processes are trying to insert a row (with the max) at the same time.

Considering that I can't change the PK, what is the best approach to solve this problem?

To sum up:

* I need to have processes in parallel inserting on T

* I can't change anything on T

* The PK is NOT an Identity

View 4 Replies View Related

Instead Of Trigger - Get Identity Value

May 22, 2008

Hi,
I have a problem with a instead of Trigger. The Trigger's target is a view.
When the Trigger is called during a query insert I must store the data inside two tables. The tables are related with each other by the primary key.
So I must: a. Insert the data in the first table. b. Get the value of the Identity c. Insert the data in the second table.
But I have a problem: the value returned of the query "SELECT ID FROM FIRST_TABLE WHERE ID = SCOPE_IDENTITY()" is null. I'm sure that the first insert query is executed.
Where is the error?

Thanks

View 8 Replies View Related

How To Refer A Column When The Referencing Column Is An Identity Column

Oct 16, 2006

Hi all,

The requirement is to have a table say 'child_table', with an Identity column to refer another column from a table say 'Parent_table'..

i cannot implement this constraint, it throws the error when i execute the below Alter query,

ALTER TABLE child_table ADD CONSTRAINT fk_1_ct FOREIGN KEY (child_id)
REFERENCES parent_table (parent_id) ON DELETE CASCADE

the error thrown is :
Failed to execute alter table query: 'ALTER TABLE child_table ADD CONSTRAINT
fk_1_ct FOREIGN KEY (child_id) REFERENCES parent_table (parent_id) ON DELETE
CASCADE '. Message: java.sql.SQLException: Cascading foreign key 'fk_1_ct' cannot be
created where the referencing column 'child_table.child_id' is an identity column.

any workarounds for this ?

View 3 Replies View Related

TSQL - Using ALTER TABLE - ALTER COLUMN To Modify Column Type / Set Identity Column

Sep 7, 2007

Hi guys,
If I have a temporary table called #CTE
With the columns
[Account]
[Name]
[RowID Table Level]
[RowID Data Level]
and I need to change the column type for the columns:
[RowID Table Level]
[RowID Data Level]
to integer, and set the column [RowID Table Level] as Identity (index) starting from 1, incrementing 1 each time.
What will be the right syntax using SQL SERVER 2000?

I am trying to solve the question in the link below:
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1

Thanks in advance,
Aldo.

I have tried the code below, but getting syntax error...



ALTER TABLE #CTE
ALTER COLUMN
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;


I have also tried:

ALTER TABLE #CTE
MODIFY
[RowID Table Level] INT IDENTITY(1,1),
[RowID Data Level] INT;







View 18 Replies View Related

@@Identity Being Over-written By Insert Trigger In Stored Procedure.

Oct 6, 2004

Hi All

I have a problem with an existing stored procedure that is used to insert a new entry to a table (using an Insert statement).

I have been using the @@Identity global variable to return the identity column (id column) back to the calling routine. This has worked fine for years until recently an ‘after insert Trigger’ has been added to the table being updated.

Now the @@Identity is returning the identity value of the trigger that was called instead of the original table insert.

Does anyone know how I can code around this issue (without using a select statement within my stored proc, as these have been known to cause locks in the past).

Thank in advance.

Eamon.

View 2 Replies View Related

GUID As Primay Column And Identity Column

Jan 9, 2007

Hello;
My Memebership table has Guid column as Primary key.
But I would like to add Auto numbering Identity column to this table.
Is this idea OK  or it will bring some problems?
Thank you in advance for your help
 

View 3 Replies View Related

Alter A Column To Be The Table Identity Column

Aug 3, 2006

i have a table
table1
column1 int not null
column2 char not nul
column3 char

i want to script a change for table1 to alter column1 to be the table identity column. not primary.

View 5 Replies View Related

Making An Existing Column An Identity Column

Mar 5, 2004

Hi,

I have a column that is unique that I would like to make into an IDENTITIY column after I insert some data into it.

I tried

alter table <table_name>
alter column <col_name> int Identity (1,1)

but it fails.


Ajay

WORD4LIFE
(http://www.word4life.com)

View 2 Replies View Related

To Make An Existing Column Become An Identity Column

Jul 20, 2005

Hi(SQL Server 2000)I have an existing table (t) with a column that is NOT an identity column(t.ID), but it has manually inserted "row numbers". I want to make thiscolumn become an identity column. This column is a key field to othertables, so I want to keep the row numbers that are allready inserted.From the Query Analyzer, how do I do this?Thanks in advance!Regards,Gunnar VøyenliEDB-konsulent asNORWAY

View 3 Replies View Related

How To Bind A Column To Identity Column Of The Different Table.

Apr 2, 2007



Hi,



I have two tables table1 and new_table



Table1 has id_value column which is int and it is idenity specification is yes and identity increment is 1 .



And I have a NEW_TABLE with column name new_id which should store current id_value of Table1.



This type of functionality is requirement for my project.



I should get a current value of id_value from table1 . if I say SELECT * FROM NEW_TABLE ;



Please help me out to fix this issue



Thanks

Purnima

View 3 Replies View Related

SQL Server 2012 :: How To Get A Table Identity Inserted By Instead Of Insert Trigger

May 14, 2015

I have a problem described as follows: I have a table with one instead of insert trigger:

create table TMessage (ID int identity(1,1), dscp varchar(50))
GO
Alter trigger tr_tmessage on tmessage
instead of insert
as
--Set NoCount On
insert into tmessage

[code]....

When I execute P1 it returns 0 for Id field of @T1.

How can I get the Identity in this case?

PS: I can not use Ident_Current or @@identity as the table insertion hit is very high and can be done concurrently.Also there are some more insertion into different tables in the trigger code, so can not use @@identity either.

View 5 Replies View Related

SQLNCLI Gives Wrong Column Data From Linked Svr

Apr 8, 2008

I am running an SSIS package to extract data from a source SQL Server database to our own database. The source is on a linked server. The package worked until a column size was increased on a table on the source.

If I run a query against the linked server table I get the following error:

'OLE DB provider 'SQLNCLI' for linked server 'GSC-RPT.SVR.BANKONE.NET' returned data that does not match expected data length for column '[GSC-RPT.SVR.BANKONE.NET].[Peregrine].[dbo].[scProblem].short_description'. The (maximum) expected data length is 100, while the returned data length is 160.'

I've tried deleting the links and recreating them. Nothing works. I don't understand why my database query is expecting a data length of 100. Why does it not pick up the definition of the field length from the linked server? How has it retained this expectation when I have deleted the linkage and recreated it?

View 2 Replies View Related

Derived Column Return WRONG Datatype.

Apr 18, 2006

Hi, friend,

l've 2 variables A & B from source database which i used to calculate C with the following formula, i.e.

C = 100 * A/B

In case of B = 0 (DIV BY ZERO), C should be equal to -9999.

l've set the datatype for A & B are INT, C is FLOAT at targetting database.

So l used to derived column to calculate C as,
B == 0 ? -9999 : 100 * A/B

After l run the package, l realize that all my C is INTEGER rather than FLOAT.....it seems that SQL server has evaluate the wrong datatype for me....anyway to overcome this?

View 4 Replies View Related

SELECT * View Returning Wrong Column Data

Oct 16, 2007

SQL 2005 9.00.3402.00 (x64) As Above really when I select * OR select a single column from the view the wrong column data is returned. in SQL Management Studio when I expand the Columns of the view it reflects the old table structure not the new table structure. I can easily fix by compiling the view again but this would mean I would have to recompile all referencing views when I make a change to table structures. I've tried various DBCC Clean Buffers & drop cache with no effect. Is there a command to recompile all views & poss stored procs in a database. Any help or explanation would be appreciated GW

View 12 Replies View Related

Reporting Services :: Why SSRS Assuming Wrong Column Datatype

Nov 3, 2015

I have an SSRS report that's being built from a stored procedure given 2 variables. The error I'm receiving is

An error has occurred during report processing. (rsProcessingAborted)
Cannot read the next data row for the dataset Sheet2. (rsErrorReadingNextDataRow)
Conversion failed when converting the varchar value '.5' to data type int.

This doesn't make any sense. The variable in question is being stored as a varchar. I don't do any typecasting in the stored procedure. Executing the stored proc in sql server studio works fine. In the query designer I replaced the stored procedure call with just the code for the stored procedure and it works fine as well. Switching it back to call the stored procedure throws the same error.

Our prod server is using 2008, our dev is running 2010, and I have 2013 Visual Studio. It has to do with the version of SSRS. I checked the datatype of the fieldtype and rd:TypeName was System.Int32. Changing it to System.String did nothing. Leaning even more towards the issue being SSRS itself. I even pulled the working version off of prod and it gave me the same error in Visual Studio.

Why is SSRS trying to convert a varchar to an int? It should be a double if anything or just leave it alone and not try typecasting it at all.

View 4 Replies View Related

Change An Int Column To Identity Column

Dec 17, 2001

Hi, I want to change an int column (not null) to identity column. I tried
the following:

alter table myTable alter column ID int identity(10, 1) not null

But it failed with the error message:

Incorrect syntax near the keyword 'identity'.

Can someone please show me the correct statement (if it exists)>

Many thanks.

View 4 Replies View Related

Trigger Doesn't Log Into The Audit Table If The Column Of Table Has Trigger On Is Null

Jan 23, 2008



Hi,

I have a trigger set on TABLE1 so that any update to this column should set off trigger to write to the AUDIT log table, it works fine otherwise but not the very first time when table1 has null in the column. if i comment out

and i.req_fname <> d.req_fname from the where clause then it works fine the first time too. Seems like null value of the column is messing things up

Any thoughts?


Here is my t-sql


Insert into dbo.AUDIT (audit_req, audit_new_value, audit_field, audit_user)

select i.req_guid, i.req_fname, 'req_fname', IsNull(i.req_last_update_user,@default_user) as username from inserted i, deleted d

where i.req_guid = d.req_guid

and i.req_fname <> d.req_fname



Thanks,
leo

View 7 Replies View Related

Sql Server 2005 Inserting Prbblem..wrong SQL? Wrong Parameter?

Feb 19, 2006

Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code:        Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _        ByVal Email As String, ByVal Gender As Integer, _        ByVal FirstName As String, ByVal LastName As String, _        ByVal CellPhone As String, ByVal Street As String, _        ByVal StreetNumber As String, ByVal StreetAddon As String, _        ByVal Zipcode As String, ByVal City As String, _        ByVal Organization As String _        ) As Boolean            'returns true with success, false with failure            Dim MyConnection As SqlConnection = GetConnection()            Dim bResult As Boolean            Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection)            MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName))            MyCommand.Parameters.Add(New SqlParameter("@Password", Password))            MyCommand.Parameters.Add(New SqlParameter("@Email", Email))            MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender))            MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName))            MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName))            MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone))            MyCommand.Parameters.Add(New SqlParameter("@Street", Street))            MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber))            MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon))            MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode))            MyCommand.Parameters.Add(New SqlParameter("@City", City))            MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization))            Try                MyConnection.Open()                MyCommand.ExecuteNonQuery()                bResult = True            Catch ex As Exception                bResult = False            Finally                MyConnection.Close()            End Try            Return bResult        End FunctionThanks!

View 1 Replies View Related

Trying To Get Identity Column

Nov 8, 2007

I have been trying to get the new sol_id that is added so I can use it in other code, but it is only returning 0. The new record is getting added to the table, but my varaiable myNewSolID is 0 in my response.write(myNewSolID)
I appeaciate any help!
Here is my steored procedure and my code:
 ALTER PROCEDURE [dbo].[AddTrackings]

@DropDate datetime,
@Comment nvarchar(100),
@DateEntered datetime,
@EnteredBy nvarchar(50),
@Sol_ID Int OUTPUT
AS
INSERT INTO tblTrackings (DropDate, Comment, DateEntered, EnteredBy)

VALUES (@DropDate,@Comment,@DateEntered,@EnteredBy)

SET @Sol_ID=SCOPE_IDENTITY()

RETURN


Sub ProcessTracking()
Dim myNewSolID As Integer

Dim ConnectStr As String = _
ConfigurationManager.ConnectionStrings("2ConnectionString").ConnectionString

Dim MySQL As String = ""

If Me.chkDropSave.Checked Then
MySQL = "AddTrackings"
Dim MyConn As New SqlConnection(ConnectStr)
Dim Cmd As New SqlCommand(MySQL, MyConn)
Cmd.CommandType = CommandType.StoredProcedure
Dim InsertedInteger As New SqlParameter("@Sol_ID", SqlDbType.Int)
InsertedInteger.Direction = ParameterDirection.Output
Cmd.Parameters.Add(InsertedInteger)

Dim MySqlParamStart As New SqlParameter("@DropDate", SqlDbType.DateTime)
Cmd.Parameters.Add(MySqlParamStart)
MySqlParamStart.Value = Me.BasicDatePickerDropDate.SelectedValue

Dim MySqlParamAmtTo As New SqlParameter("@Comment", SqlDbType.NVarChar, 100)
Cmd.Parameters.Add(MySqlParamAmtTo)
MySqlParamAmtTo.Value = Me.txtComment.Text

Cmd.Parameters.AddWithValue("@DateEntered", Now())
Cmd.Parameters.AddWithValue("@EnteredBy", Profile.UserName)

Try
MyConn.Open()
myNewSolID = Cmd.ExecuteScalar()Response.Write(myNewSolID)

Catch ex As Exception
Response.Write(ex.Message)
Finally
MyConn.Close()
End Try

Else
End If
End Sub 

View 8 Replies View Related

Is Identity Column?

Apr 24, 2008

Hi to all,             Using sql query i want to check whether the column is Identity column or not? Please members reply me.         

View 2 Replies View Related

Identity Column - DTS

Mar 27, 2001

Having an identity column in target table, how do I use SQL 7 DTS ?

In Import wizard, I chose 'ignore' in source, checked Enable Indenity Insert - DTS failed with a msg like - a column cannot have NULL...

In DTS package, had Fast load and Enable Indenity checked - no luck !

Unchecked Enable identity, do luck.

What would be the standard procs for this since this sounds like a very common
senario.
What I am expecting is to insert from source and SQL would take care identity column automatically.
Appreciate your help very much !

-Ivan

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

View 1 Replies View Related

Identity Column

Sep 17, 2001

I want to know how to reset the identiy column when i delete a row from the existing table.

Example:

I have a table a with id (identity incr value 1) and name

I have 10 records inserted into it.,which would be from 1...thru...10 i delete a record say 5 then i have 1,2,3,4,6,7,8,9,10, now i want them to be rearranged to systamatic order like 1,2,3,4,5,6,7,8,9 how can i do this.


Thanks in advance,

Shaheen

"God Bless America"

View 2 Replies View Related







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