Hi all!
In a insert-trigger I have two joins on the table named inserted.
Obviously this construction gives a name collition beetween the two
joins (since both joins starts from the same table)
Ofcourse I thougt the using
bla JOIN bla ON bla bla bla AS a_different_name would work, but it
does not. Is there a nice solution to this problem?
Any help appriciated
is there any way of getting the identity without using the "@@idemtity" in sql??? I'm trying to use the inserted event of ObjectDataSource, with Outputparameters, can anybody help me???
View 1 Replies View RelatedI am working on sql integrating with Hybris. Most of you are not aware of hybris as it was a new technology.
In hybris we will create some classes and in those classes we will insert some tables. These tables will be automatically inserted into the sql server. No need to manually insert them the hybris structure will insert it. So my doubt is, how can we find out whether all the records have been inserted into sql database or not how we can check that.
Is there any way?? We have those classes in wich we can see which tables are there. Or else from the data model itself we can see what are the tables avilable or shud be inserted into sql thru hybris.
Can we check by giving all the table names in a single query?? or is there any other way to find out. If we can check by giving all the table names in single query how we need to give it.
SQL 7 SP2
Are the tables inserted and deleted available from within a sp which is called from a trigger ?
Craig
I am reading the WROX "Professional SQL Server 7 Programming" book.
The following code appears on page 424:
CREATE TRIGGER ProductIsRationed
ON Products
FOR UPDATE
AS
IF EXISTS
(
SELECT 'True'
FROM Inserted i
JOIN Deleted d
ON i.ProductID = d.ProductID
WHERE (d.UnitsInStock - i.UnitsInStock) > d.UnitsInStock / 2
AND d.UnitsInStock - i.UnitsInStock > 0
)
BEGIN
RAISERROR('Cannot reduce stock by more than 50%% at once.',16,1)
ROLLBACK TRAN
END
The trigger fires when an UPDATE is made to Products table. The author states that the Inserted and Deleted tables only exist for the life of the trigger, not before, and not after the trigger runs. If this is true, then why would there be any rows in the Deleted table in this case? No rows were
deleted within the trigger. As far as I can see, no rows have been updated
either. If the condition does exist, no rows will be updated, and an error will be displayed. Otherwise, the row will be updated. Then there would be a row in the inserted table. But then the trigger is finished and the inserted table for that trigger disappears. I think my logic is flawed, which is why I am writing. I don't think I fully understand the Inserted and Deleted tables.
Any help would be greatly appreciated.
Nathan
I want to know how inserted and deleted temp tables in SQL server work. My question is more regarding how they work when multiple users accessing the same database. Suppose two users update the database at the same time. In that case what are the values stored in the inserted and deleted tables.
I have a trigger that records changes to the database as in an audit trail. Like any other audit trail I insert data into my audit table from the inserted and deleted temp tables in MS SQL Server. I however am not clear as to how these inserted and deleted tables store values when two users update the database at the same time. Are there separate inserted and deleted tables for each session. The users access the database thru ASP pages.
The audit trail I am trying to use is http://www.nigelrivett.net/AuditTrailTrigger.html
I actually would like to store the inserted and deleted temp tables into other temporary tables so that I can access these tables thru a stored procedure. This is when the problem of same users updating the temporary tables is more pronounced.
Thanks in advance.
Hi all,
I just ran across an issue on a SQL 2000 sp4 db where RI was being maintained solely with triggers. I am attempting to change the primary key of a parent table and cascade the results to all its children without using the vendor-supplied trigger code (long story...) using an INSTEAD OF trigger.
My question is: does SQL Server create any kind of relationship between the inserted and deleted tables that I could exploit since the key field is unavailable?
I am trying to avoid having to add a surrogate key to each of the children just for this activity (as there are many M rows in each and no other suitable unique column combinations that span all the child tables).
-DC
I'm know the above tables are system generated, am I right in assuming the following.
1) The tables are unique to the current user.
2) The tables only last as long as the transaction that caused their creation
Thanks in advance
Alex
Hello,
I have a stored procedure that's running a little slower than I would like. I've executed the stored proc in QA and looked at the execution plan and it looks like the problem is in a trigger on one of the updated tables. The update on this table is affecting one row (I've specified the entire unique primary key, so I know this to be the case). Within my trigger there is some code to save an audit trail of the data. One of these statements does an update of the history table based on the inserted and deleted tables. For some reason this is taking 11.89% of the batch cost (MUCH more than any other statement) and within this statement 50% of the cost is for a table scan on inserted and 50% is for a table scan on deleted. These pseudo-tables should only contain one record each though.
Any ideas why this would be causing such a problem? I've included a simplified version of the update below. The "or" statements actually continue for all columns in the table. The same trigger template is used for all tables in the database and none of the others seem to exhibit this behavior as far as I can tell.
Thanks for any help!
-Tom.
UPDATE H_MyTable
SET HIST_END_DT = @tran_date
FROM H_MyTable his
INNER JOIN deleted del ON (his.PrimaryKey1 = del.PrimaryKey1) and
(his.PrimaryKey2 = del.PrimaryKey2)
INNER JOIN inserted ins ON (his.PrimaryKey1 = ins.PrimaryKey1) and
(his.PrimaryKey2 = ins.PrimaryKey2)
WHERE (his.HIST_END_DT is null)
and ((IsNull(del.PrimaryKey1, -918273645) <>
IsNull(ins.PrimaryKey1, -918273645)) or
(IsNull(del.PrimaryKey2, -918273645) <>
IsNull(ins.PrimaryKey2, -918273645)) or
(IsNull(del.Column3, -918273645) <>
IsNull(ins.Column3, -918273645)))
I want to compare the before and after values of an UPDATEd column using a trigger. I want to know if the value in the column has changed. Simple? No!
As you know, SqlServer puts the before image of the UPDATEd rows into the DELETED virtual table and the after image of the UPDATEd rows in the INSERTED virtual table.
So you would get the before and after data by doing a SELECT on these tables.
But here is the problem - how do you join the tables? What if there are >1 rows in these 2 tables (because the UPDATE affected >1 rows) - how do i know which "old"/DELETED rows correspond to which "new"/INSERTED?"
Ok - I could join the 2 tables on the primary key, but what if the primary key was updated? In that case the join would not work - the DELETED table would contain the old primary key value and the INSERTED table would contain the new (different) primary key value. In fact, ALL of the columns may have been changed by the UPDATE.
Now, there is another thing to try with triggers - the
IF UPDATE ( <columname> )
test. This is designed to tell you if a specified column was UPDATEd by the last UPDATE. However, this will return TRUE for any UPDATE that mentions the column - even if the UPDATE does not change any data! So I cannot determine whether a certain column has had its value changed with this either.
So then you can try another test mentioned in the docs for CREATE TRIGGER - the
IF COLUMNS_UPDATED()
test. However, this will report that a column has been updated, NOT whether the data has changed as aresult of that UPDATE.
So if you UPDATE the value in the column to the same value as it was beforehand (admittedly, a pointless thing to do, but it could happen in some apps), this fuction will say, yes, this column was updated.
So my question remains - how do I know if the data has changed in a column after an UPDATE, using a trigger?
Any ideas?
I dont know what I am doing wrong. The trigger (see below) is doing what I want it to do when I do this:
INSERT INTO dbo.personal
(personal_id, chef_id,fornamn, efternamn)
VALUES
(40, 100, 'Malin', 'Markusson' , 'Boss')
but when I remove one value, the result in the logtable is NULL:
INSERT INTO dbo.personal
(personal_id, chef_id,fornamn, efternamn)
VALUES
(40, 100, 'Malin', 'Markusson' )
How can I change the trigger so that it will give me the information of the values that have been updated, inserted or deleted when I dontchange all values (just a couple of them)?
My trigger:
CREATE Trigger trigex
ON dbo.personal
FOR insert, update,delete
AS
INSERT INTO logtable (Innan_värde, Ny_värde)
SELECT
rtrim(cast(d.personal_id as varchar)+', '+cast(d.chef_id as varchar)+', '+rtrim(d.efternamn)+', '+ rtrim(d.fornamn)+ ', '+ rtrim(d.titel)),
(cast(i.personal_id as varchar)+', '+cast(i.chef_id as varchar)+', '+rtrim(i.efternamn)+', '+ rtrim(i.fornamn)+ ' '+ rtrim(i.titel))
FROM inserted i full join deleted d on i.personal_id = d.personal_id
My table:
CREATE Table logtable
(Innan_värde varbinary(max),
Ny_värde varbinary(max))
Thank you !
can anybody tell me with an example how to use Inserted and Deleted in Sql Server 2005
View 7 Replies View RelatedI have a CRM database that has a lot of tables and would like to be able to extract the last 'x' records in descending order from each table based on a common a field 'modifiedon' that is in every table and is auto populated by the system.
View 4 Replies View RelatedWe have an app that uses triggers for auditing. Is there a way to know the order that the records were inserted or deleted? Or maybe a clearer question is.... Can the trigger figure out if it was invoked for a transaction that "inserted and then deleted" a record versus "deleted and then inserted" a record? The order of these is important to our auding.
Thanks!
CB
I have a table that sometimes has modifications to column(s) comprising the primary key [usually "end_date"]. I need to audit changes on this table, and naturally, turned to after triggers.
The problem is that for updates, when the primary key composition changes, I'm not able to relate/join using the primary key - obviously, it no longer matches across INSERTED and DELETED. Now, for a single row update, it's easy to check for updates on PK columns and then deduce what changes were made...
So the real question is: are rows in INSERTED and DELETED always in matching order (1st row in INSERTED corresponds to the 1st row in DELETED...)?
I don't want to put a surrogate key (GUID nor IDENTITY) on the base table if at all possible. INSERT... SELECT from the inserted/deleted tables into a temp table with identity column is fine, and is what I'm currently doing; I would like MVP or product engineer level confirmation that my ordering assumption is correct.
Testing using an identity surrogate key on base table, and selecting from the Ins/del tables, and the temp tables without an order by clause seems to always return in proper order (proper for my purposes). I've tested under SQL 2005 RTM, SP1, SP2, and SP2 "3152".
FYI, I've lost the debate that such auditing is better handled by the application, not the database server...
Aside: why doesn't the ROW_NUMBER() function allow an empty OVER( ORDER BY() ) clause? Will SQL ever expose an internal row_id, at least in the pseudo tables, so we can work around this situation?
Thanks
Mike
I have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails.
I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record.
And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.
Hi folks,
Table:
a int,
b int,
c int,
d text
I need to change my AFTER - Trigger from this (example!):
select * into #ins from inserted
to something like
select *(without Text / nText / image -columns) into #ins from inserted.
So I tried to build a string like this: (using INFORMATIONSCHEMES)
select @sql = 'select a,b,c into #ins from inserted'
exec(@sql)
a,b,c are not of Text, nText or Image datatype.
After executing the trigger, I get an error, that inserted is unknown.
Does anyone know how to solve this ?
Thx.
I would like to wrap the following code in a function and reuse it. Â I use this code in many triggers.
DECLARE @Action as char(1);
SET @Action = (CASE WHEN EXISTS(SELECT * FROM INSERTED) AND EXISTS(SELECT * FROM DELETED)
THEN 'U' Â -- Set Action to Updated.
WHEN EXISTS(SELECT * FROM INSERTED)
THEN 'I' Â -- Set Action to Insert.
WHEN EXISTS(SELECT * FROM DELETED)
THEN 'D' Â -- Set Action to Deleted.
ELSE NULL -- Skip. It may have been a "failed delete". Â Â
END)
Is it possible to write a function and pass the INSERTED and DELETED logical tables to it?
We're getting
Msg 1013, Level 16, State 1, Line 1
The objects "MYTEST2.TEST" and "mytest.TEST" in the FROM clause have the same exposed names. Use correlation names to distinguish them.use of fully qualified names is allowed without having to alias them.One workaround is changing compatibility mode to 80.Is there another (startup flag?).Reason for no alias: MS Reportbuilder doesn't provide them when building queries
CREATE SCHEMA MYTEST;
go
CREATE SCHEMA MYTEST2;
go
CREATE TABLE MYTEST.TEST
[code]....
I'm doing a data migration job from our old to our new database. The idea is that we don't want to clutter our new tables with the id's of the old tables, so we are not going to add a column "old_system_id" in each and every table that needs to be migrated.
I have created a number of tables in a separate schema "dm" (for Data Migration) that will store the link between the old database table and the new database table. Suppose that the id of a migrated record in the old database is 'XRP002-89' and during the insert into the new table the IDENTITY column id gets the value 1, the link table will hold : old_d = 'XRP002-89', new_id = 1, and so on.
I knew I can get the value of IDENTITY columns with the OUTPUT INTO clause, although I have never actually used it. And now I can't get it to do what I need.
Below is some code to set up three tables: the old table, the new one, and the table that will hold the link between the id's of records in the old database table and the new database table.
Code:
CREATE TABLE DaOldTable(
pkCHAR(10)NOT NULL,
a_columnCHAR(10)
)
CREATE TABLE DaNewTable(
idINTNOT NULL IDENTITY,
[Code] ....
Below I tried to use the OUTPUT INSERTED INTO clause. Beside getting the generated IDENTITY value, I also need to capture the value of the old id that will not be migrated to the new table. When I use "OUTPUT DaOldTable.pk" the system gives me the error: "The multi-part identifier "DaOldTable.pk" could not be bound." Using INSERTED .id gives no problem.
Code:
INSERT INTO DaNewTable(a_column)
--OUTPUT DaOldTable.pk, INSERTED.id link_old2new_DaTable--(DaOld_id, DaNew_id)
SELECT a_column
FROM DaOldTable
[Code] ...
--but getting "The multi-part identifier "DaOldTable.pk" could not be bound."
DROP TABLE DaOldTable
DROP TABLE DaNewTable
DROP TABLE link_old2new_DaTable
How can I populate a table that must hold the link between the id's of records in the old database table and the new database table? The records are migrated with set-based inserts.
I will paste my code below. Â I inserted a breakpoint but nothing is being sent to the database and nothing came up when I ran it with the breakpoint. Â Can anyone tell me how to fix this?Protected Sub btn_addfriend_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_addfriend.Click Dim add_friend_source As New SqlDataSource add_friend_source.InsertCommand = "INSERT INTO [Friends] ([UserName], [UserID], [IP], [AddedOn], [FriendName]) VALUES (@UserName, @UserID, @IP, @AddedOn, @FriendName)" detailsview_addfriend.DataSource = add_friend_source add_friend_source.DataBind() add_friend_complete.Text = "Success!" End SubThe "Success!" text is the only thing that seems to work properly...
View 7 Replies View RelatedHello, I am stumped on how to get the last id that was inserted. I have searched all over the internet to find the answer but all of them turned up to be classic asp.
View 4 Replies View Relatedwhile executing this command locally, ita working fine
Insert into tblorderDetails
(OrderID,ProductID,SofaPackageID,Quantity,UnitPrice,TotalOrd,ItemStatus )
values(1, 3915, 0, 1, 2049.00, 2049.00, 'PO')
but when executing online, giving me following msg:
(1 row(s) affected)
Msg 207, Level 16, State 1, Line 1
Invalid column name 'Location16FreeStock'.
and also data is not inserted in table.
I have a program to insert rows into a SQL Server table from an ASCII file. There appears to be some data mapping going on. For example:
"J & J Smith" is imported as "J _J Smith".
This doesn't happen on all servers. I can run it against my database and get the desired results.
I looked into the Server Configuation. Under the Security tab there are Mapping options. It appears that you can map _ to something else, but I don't see where you can map & to anything.
hi,
this is my spc:
select top 1 u.userid,
u.user_name,
u.password,
c.code_description as role_code,
u.expiry_date,
u.created_date,
u.active,
convert(varchar,v. user_date, 103) + ' ' + right('0' + stuff(right(convert(varchar,v.user_date, 109), 14), 8, 4, ''), 10) as user_date,
v.operation,h.user_name as updatedby
from [usermaster] u inner join [codeMaster] c
on 'sp'=c.code inner join [HRUser_developerlog] v on u.userid=v.inserted_id and v.operation='update' inner join [usermaster] h on v.userid=h.userid
where u.userid = '3' order by v. user_date
if i use it gives the v.user_date as fist modified date it is not giving last modified date.
select * from HRUser_developerlog
user_date operat userid
ion
2007-01-25 14:28:17.000insert1
2007-01-24 13:02:18.093insert4
2007-03-03 11:30:29.310update2
2007-03-03 11:30:55.373insert3
2007-03-03 11:31:31.717insert26
2007-01-25 14:28:17.000insert3
2007-03-03 11:43:39.733update26
2007-03-03 11:48:04.543delete3
2007-03-03 14:26:22.420update3
2007-03-03 14:27:00.280update3
2007-03-03 14:27:12.013update2
2007-03-03 14:27:35.763update1
2007-02-08 14:28:17.030update2
2007-03-03 14:27:55.967update3
2007-03-03 14:29:18.827update3
2007-03-03 14:30:52.983update3
so it has to show
2007-03-03 14:30:52.983
but it shows the first updated id only
2007-03-03 14:26:22.420
please help me to get my need
I have a dataset that uses generated stored procedures to do its select, insert, update, delete. I am inserting a row to that dataset, and after the update, using the ID of newly created row. This worked just fine until I added triggers to some of the tables on my DB, and now, when I insert a row, the row's ID is not available after the update (it's 0) Any idea what happened / what I have to do to fix this? Thnx!
View 1 Replies View Relatedhow do i insert a record, then return the inserted record back to VS (ASP + VB) to display? maybe just the ID will be enough. then i will do a select
View 6 Replies View RelatedHi,I use a Stored Procedure who works very well....INSERT INTO Computers (CategoryID, SubCategoryID, ......VALUES (@CategoryID, @SubCategoryID, .........But as soon as it creates the new row, i want to be able to get the Id (ComputerId) of this row. I use ComputerId as the primary key.How can i do that? I code in VB.Thanks
View 2 Replies View RelatedI'm creating a web application that has user input on 3 seperate pages. Each page prompts the user for specific information and at the bottom of the page the user will click on the "Submit" button to post the info from the form to the database table.
Once the user has submitted the first page of information how do I retrieve the ID from the CustID column of that row so I can use the Update function to add additional info to that row when the user clicks on the submit button on page 2 & 3. I don't want to hold all the information in variables until the end in case they bail out of the form.
TIA
Steve
I've seen a lot of info on how to do this with @@IDENTITY and SCOPE_IDENTITY(), but can't get this to work in my situation.
I am inserting a record into a table. The first field is a GUID (UNIQUEIDENTIFIER) that uses newid() to generate a unique GUID. Since I am not using an int, I can't set the IsIdentity property of the field. Without IsIdentity set, @@IDENTITY and SCOPE_IDENTITY() do not work.
How can I get the ID (or whole record) of the last record that I inserted into a SQL database? Note that I am doing this in C#.
As a last resort, I could generatate the GUID myself before the insert, but I can't find C# code on how to to this.
Hi!
I would like to get the last inserted ID in a sql 2005 table to a variable.My code is:
SqlDataSource ds = new SqlDataSource();ds.ConnectionString = ConfigurationManager.ConnectionStrings["ConnString"].ToString();ds.InsertCommandType = SqlDataSourceCommandType.Text;ds.InsertCommand = "INSERT INTO Products(SellerID, Price) OUTPUT INSERTED.ProductID VALUES(@SellerID,@Price)"; ds.InsertParameters.Add("SellerID", "Sony"); ds.InsertParameters.Add("Price", "123");string output = ds.Insert().ToString(); // it's return rowsAffected but I need productID (SCOPE_IDENTITY or @@IDENTITY )Thank you for your time and help :)
How would I get the value of a ROWGUID column of the row I just inserted? (like using @@identity for an identity column.)
Thanks!
Hello there!
it was a while since i studied SQL and that brings us to my problem...
I'm creating a Stored Procedure wich first insert information in a table. That table has a uniqueidentifier fild that is default-set to newid().
later in the SP i need that uniqueidentifier value? how do I get it?
I tried this:
CREATE PROCEDURE spInsertNews
@uidArticleId uniqueidentifier = newid,
@strHeader nvarchar(300),
@strAbstract nvarchar(600),
@strText nvarchar(4000),
@dtDate datetime,
@dtDateStart datetime,
@dtDateStop datetime,
@strAuthor nvarchar(200),
@strAuthorEmail nvarchar(200),
@strKeywords nvarchar(400),
@strCategoryName nvarchar(200) = 'nyhet'
AS
INSERT INTO tblArticles
VALUES( @uidArticleId,@strHeader,@strAbstract,@strText,@dt
Date,@dtDateStart,@dtDateStop,@strAuthor,@strAutho
rEmail,@strKeywords)
declare @uidCategoryId uniqueidentifier
EXEC spGetCategoryId @strCategoryName, @uidCategoryId OUTPUT
INSERT INTO tblArticleCategory(uidArticleId, uidCategoryId)
VALUES(@uidArticleId, @uidCategoryId)
But i get an error when I EXEC the SP like this:
EXEC spInsertNews
@strHeader = 'Detta är den andra nyheten',
@strAbstract = 'dn första insatt med sp:n',
@strText = 'här kommer hela nyhetstexten att stå. Här får det plats 2000 tecken, dvs fler än vad jag orkar skriva nu...',
@dtDate = '2003-01-01',
@dtDateStart = '2003-01-01',
@dtDateStop = '2004-01-01',
@strAuthor = 'David N',
@strAuthorEmail = 'david@davi.com',
@strKeywords = 'nyhet, blajblaj, blaj'
the errormessage is: Syntax error converting from a character string to uniqueidentifier.
does anyone have a sulution to this problem?
Can I use something similar to the @@IDENTITY?
I will be greatful for any ideas...
thanks
/David, Sweden