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.
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 am using SQL Server 2000.I want to create an after insert trigger on one of my tables, but I have forgotten how I reference the inserted data to do some business logic on it. Can someone please help. Thanks Jag
I'm hoping someone has seen this before because I have no idea what could be causing it. I have an SQL 2005 database with multiple tables and several triggers on the various tables all set to run after insert and update. My program inserts a record into the "items" via a SP that returns the index of the newly added row. The program then inserts a row into another table that is related to items. When the row is inserted into the second table it gets an error that it cannot insert the record because of a foreign key restraint. Checking the items table shows the record that was just inserted in there is now deleted. The items record is only deleted when I have my trigger on that table enabled. Here is the text of the trigger: GOSET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOALTER TRIGGER [dbo].[TestTrigger]ON [dbo].[items]AFTER INSERT AS BEGIN SET NOCOUNT ON; INSERT INTO tblHistory(table_name, record_id, is_insert) VALUES ('items', 123, 1) END tblHistory's field types are (varchar(50), BigInt, bit). As you can see there is nothing in the trigger to cause the items record to be deleted, so I have no idea what it could be? Anyone ever see this before? Thanks in advance!
hi, i am writing a trigger to log inserts,updates and deletes in a table and would like to also enter the user details ie who did the transaction. is the spid in the inserted table? if not how do i get this information?
i have a trigger, so need to use the "inserted" table that comes in.
however, i need to use this "inserted" in the following way:
EXEC @LRES = sp_executesql N' INSERT INTO newtable (col1,col2) select * from (select acol1,acol2 from inserted WITH (NOLOCK))
however i keep on getting the error that he doesn't know the object "inserted".... I i have the feeling that i may not use inserted at that stage.
what now?
i really need to use the sp_executesql result in the @LRES since after it we should perform some actions in other tables, depending on the result of the @LRES. anyway i really hope anyone can help me as soon as possible.
How would i get the value of a field that i just inserted and put that into a parameter, so that i could update another table.
This is the code that i used in the trigger that did not work: @field1 = select srcfield1 from inserted
Anyway here is the full code:
CREATE TABLE Source (srcID int IDENTITY, srcField1 nvarchar(50)) CREATE TABLE Destination (destID int IDENTITY, destField1 nvarchar(50)) go
CREATE TRIGGER tr_SourceInsert ON [dbo].[Source] FOR INSERT @Field nvarchar(50) output AS SELECT @Field1 = SELECT Field1 FROM inserted UPDATE Destination SET Field1 = @Field where destID = '1' go
How this copy the entire contence of the table into the archive each time someone insert a row. How do I get it to only insert the row which had triggered the insert? Ed
Is there an alternative to using FETCH to loop through the Inserted/Delete Tables within a trigger? Does this work?
SELECT * FROM Inserted BEGIN
if INSERTED.IsActive then ...
END
Would this only see the first record?
Currently I'm doing the following;
AS DECLARE @JobID INTEGER; DECLARE @IsActive BIT; DECLARE Temp CURSOR FOR SELECT JobID, IsActive FROM Inserted; BEGIN OPEN Temp; FETCH NEXT FROM Temp INTO @JobID, @IsActive; WHILE (@@FETCH_STATUS = 0) BEGIN
if @IsActive then ...
FETCH NEXT FROM Temp INTO @JobID, @IsActive; END; CLOSE Temp; DEALLOCATE Temp;
Is this the best method for looping through the Deleted/Inserted or any other table within a trigger?
Hi all, I have a ranking system where I wish to update the ranking every time a result is reported. Performance is no issue what-so-ever. More specifically, two players are to devide their points after each series of games to reflect the fraction of over-all games that each player have won. I've written the trigger below, but Visual Web Developer 2005 Express (SQL Server 2005 Express) complains about the references to the 'inserted'-table. I find it very difficult to transform the code below to something that looks like the examples found in documentation. Could someone get me started in the right direction? Thanks in advance, Anders create trigger result_insert on result after insert as begin declare @won1 as int declare @won2 as int declare @oldRank1 as float declare @oldRank2 as float declare @oldranksum as float
select @won1 = sum(wongames1) from result where player1 = inserted.player1 and player2=inserted.player2 select @won2 = sum(wongames2) from result where player1 = inserted.player1 and player2=inserted.player2
select @oldrank1 = Rank from RankingInfo where memberid = inserted.playerid1 select @oldrank2 = Rank from RankingInfo where memberid = inserted.playerid2
set @oldranksum = @oldrank1 + @oldrank2
update rankingInfo set Rank = @won1 / ( @won1+@won2) * @oldranksum where memberid = inserted.player1 update rankingInfo set Rank = @won2 / ( @won1+@won2) * @oldranksum where memberid = inserted.player2
I really need your help now, and I know I can always count on this group for tough answers to tough questions. OK, here's my dilemma. I have my trigger, which upon a record being inserted into db1.table1, inserts the same record into db2.table2 (SQL 7 db on the same server). What's happening is only a few of the fields are getting over there, but most are ending up NULL or 0. Everything besides the following records are inserting into the other database table properly:
Note: The following fields have their Default Value set to (0) SystemID EventStatusID Coop NewProductFlag TVSupportFlag RadioSupportFlag FSISupportFlag RollbackPricing PleaseContactFlag
Default Value set to (1): KitInformationID
Here is the trigger:
CREATE TRIGGER EmoesImport ON mc_events FOR INSERT AS IF @@ROWCOUNT<>0
BEGIN
SET IDENTITY_INSERT mcweb2.dbo.mc_events ON
DECLARE @SystemID int DECLARE @EventID int DECLARE @AccountID int DECLARE @BillingContactID int DECLARE @EventName varchar(100) DECLARE @EventStatusID tinyint DECLARE @Coop bit DECLARE @CoopSupplier varchar DECLARE @SamplesPerDay int DECLARE @BrochuresPerDay int DECLARE @AverageDailyMovement int DECLARE @SalesGoal int DECLARE @NumberofDays int DECLARE @NumberofHours int DECLARE @NumberofStores int DECLARE @WeekNumber tinyint DECLARE @PreferredHourID tinyint DECLARE @PreferredHourother char(20) DECLARE @PreferredDate1 varchar(20) DECLARE @PreferredDate2 varchar(20) DECLARE @NewProductFlag bit DECLARE @TVSupportFlag bit DECLARE @RadioSupportFlag bit DECLARE @FSISupportFlag bit DECLARE @RollbackPricing bit DECLARE @PleaseContactFlag bit DECLARE @EventStoreSelection tinyint DECLARE @EventClusterID int DECLARE @KitInformationID tinyint DECLARE @KitDescription varchar(1000) DECLARE @KitOther varchar(200) DECLARE @MCProgNum varchar(7) DECLARE @rowguid uniqueidentifier
SELECT @SystemID = SystemID FROM INSERTED SELECT @EventID = EventID FROM INSERTED SELECT @AccountID = AccountID FROM INSERTED SELECT @BillingContactID = BillingContactID FROM INSERTED SELECT @EventName = EventName FROM INSERTED SELECT @EventStatusID = EventStatusID FROM INSERTED SELECT @Coop = Coop FROM INSERTED SELECT @CoopSupplier = CoopSupplier FROM INSERTED SELECT @SamplesPerDay = SamplesPerDay FROM INSERTED SELECT @BrochuresPerDay = BrochuresPerDay FROM INSERTED SELECT @AverageDailyMovement = AverageDailyMovement FROM INSERTED SELECT @SalesGoal = SalesGoal FROM INSERTED SELECT @NumberofDays = NumberofDays FROM INSERTED SELECT @NumberofHours = NumberofHours FROM INSERTED SELECT @NumberofStores = NumberofStores FROM INSERTED SELECT @WeekNumber = WeekNumber FROM INSERTED SELECT @PreferredHourID = PreferredHourID FROM INSERTED SELECT @PreferredHourother = PreferredHourother FROM INSERTED SELECT @PreferredDate1 = PreferredDate1 FROM INSERTED SELECT @PreferredDate2 = PreferredDate2 FROM INSERTED SELECT @NewProductFlag = NewProductFlag FROM INSERTED SELECT @TVSupportFlag = TVSupportFlag FROM INSERTED SELECT @RadioSupportFlag = RadioSupportFlag FROM INSERTED SELECT @FSISupportFlag = FSISupportFlag FROM INSERTED SELECT @RollbackPricing = RollbackPricing FROM INSERTED SELECT @PleaseContactFlag = PleaseContactFlag FROM INSERTED SELECT @EventStoreSelection = EventStoreSelection FROM INSERTED SELECT @EventClusterID = EventClusterID FROM INSERTED SELECT @KitInformationID = KitInformationID FROM INSERTED SELECT @KitDescription = KitDescription FROM INSERTED SELECT @KitOther = KitOther FROM INSERTED SELECT @MCProgNum = MCProgNum FROM INSERTED SELECT @rowguid = rowguid FROM INSERTED
I am trying to create a trigger on a table but when I check the syntax it tells me that "The column prefix 'inserted' does not match with a table name or alias used in this query"
CREATE TRIGGER trg_Structural_GenerateBarcode ON [dbo].[tbStructuralComponentSchedule] AFTER INSERT AS DECLARE @iCount int, @cBarcode char (25), @cCode char(4) DECLARE @cProject char(7), @cComponent char(10), @iEntryID int
I need to add the row number or record number to the 'inserted' and 'deleted' tables in a trigger.
Among other things, I have tried-
SELECT 1 as rowId, i.* INTO #ins FROM inserted i if @@ROWCOUNT > 1 --if multiple rows, set row number begin
SELECT @pkWhere = coalesce(@pkWhere + ' and ', ' where ') + PKF.colName + ' <= i.' + PKF.colName FROM #primaryKeyFields PKF set @strSQL = 'update #ins set rowId = (Select Count(*) From #ins i' + @pkWhere + ')'
exec (@strSql)
end
-the above sets rowId for every record to the total instead of sequential. Keep in mind that this code is used to create a trigger for any table, hence I cannot specify any column names literally.
I like to use the table "Inserted" within exec(), but it doesn't work because the scope is different. Does anyone have some sort of solution to this problem? The reason I am doing it this way is because I have a table consist of 200+ columns of bit types that contains permission information (The worest design i have ever seen!).
Code Snippet --gather column names
declare @ScreenPermissions nvarchar(256)
declare c_Permission cursor for SELECT [name] FROM syscolumns WHERE id = ( SELECT id FROM sysobjects WHERE type = 'U' AND [NAME] = 'ScreenPermissions' ) and [name] like 'Allow%'
open c_Permission fetch next from c_Permission into @ScreenPermissions while @@fetch_status = 0 begin
exec('INSERT INTO EmployeeInRoles (EmployeeID, RoleID) ' + 'select i.EmployeeID, r.RoleID ' + 'from inserted as i ' + ' inner join ScreenPermissions AS sp on sp.EmployeeID = i.EmployeeID and sp.' + @ScreenPermissions + ' = 1 ' + ' inner join Roles AS r on r.LoweredRoleName = Lower(' + '''' + @ScreenPermissions + '''' + ')' )
fetch next from c_Permission into @ScreenPermissions end close c_Permission DEALLOCATE c_Permission
We have a column syncUpdate in some tables and we need a trigger (or one for each table) which will set the current dateTime for the syncLastUpdate (dateTime) when either the row is inserted or updated (we have to ignore the syncLastUpdate column itself as this would be an infinite loop, I think).
I don't know much about DB but I think that is easly doable.
I created manage update trigger to react on one column changes. There is an application which is working with DB, so I don't have access to SQL query which changes this column. In most cases trigger works fine, but in one case when this column changes, trigger is fired and IsUpdatedColumn is true for this column, but both inserted and deleted table are empty, so I can't get new value for this column. Any idea why is it happened? Is any way around?
This column type is uniqueidentifier. Inserted and deleted tables are empty when application is changing value from NULL to not null value, but if I change it myself from Management Studio inserted table contains right values. Most like problem is in query which is changing that value.
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
We 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.
I create a Trigger that allows to create news row on other table.
ALTER TRIGGER [dbo].[TI_Creation_Contact_dansSLX] ON [dbo].[_IMPORT_FILES_CONTACTS] AFTER INSERT AS
[code]...
But if I create an INSERT with 50 rows.. My table CONTACT and ADDRESS possess just one line.I try to create a Cursor.. but I had 50 lines with an AdressID and a ContactID differently, but an Account and an AccountId egual on my CONTACT table :
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.
When i debug a trigger is it possible to add a WATCHon the INSERTED or DELETED?I think not, at least I couldn't figure out a way to do so.Does someone have a suggestion on how I can see the values?I did try to do something likeINSERT INTO TABLE1(NAME)SELECT NAME FROM INSERTEDbut this didn't work. When the trigger completed and Iwent to see the TABLE1, there were no records in it.Are there any documents, web links that describe waysof debugging the trigger's INSERTED and DELETED?Thank you
The table in the database has a field called LabID. That field is aninteger and consists of the year plus a counter. For example, thefirst record of 2006 would be "20060001," the second record of 2006would be "20060002" and so on. I'm trying to create an Insert triggerthat can generate the ID value when a new record is inserted, but I'mnot quite sure how to implement that trigger. Can anyone help?
Within a trigger, I'm trying to create a unique table name (using the NEWID()) which I can store the data that is found in the inserted and deleted tables.
I am sure someone must have run into this before. I have a couple of tables with a parent child relationship.
I created a trigger on the insert of the parent but don't want it to fire until both the parent and child have been inserted into.
However sometimes the child may not get inserted in to at all. In other words it is a 1 to 0 or more relationship.
I created the whole insert into the parent and the child and wrapped it all up in a transaction hoping that the trigger would not fire until the transaction actually completed.
However such is not the case and it fires when the parent is inserted into but nothing is inserted into the child yet even though that is part of the transaction.
Is it possible to postpone trigger fire until after both parent and child table values have been inserted?
I have create a recursive triggers in tblIncident. The PKID is the primary key in tblIncident, in this trigger, i'm trying to generate an auto increament primary key from the stored procedure GetMaxId and update to tblINcident, but I face a problem where PKID does not refresh the latest PKID, it always show the default value 0, until I requery the table. How to get the latest PKID?
/* Trigger in tblIncident */ CREATE TRIGGER GetPKID ON tblIncident FOR INSERT AS DECLARE @PKID int DECLARE @NEWVALUE int DECLARE PKID_Cursor CURSOR FOR SELECT tblIncident.PKID FROM tblIncident, inserted where tblIncident.PKID = Inserted.PKID OPEN PKID_Cursor BEGIN FETCH NEXT FROM PKID_Cursor INTO @PKID
WHILE (@@fetch_status = 0) BEGIN SET @NEWVALUE = 0 /*Call stored procedure - getmaxid to get the latest PKID */ EXECUTE GetMaxId "IN", @NEWVALUE OUTPUT Update tblIncident SET PKID = @NEWVALUE WHERE PKID = @PKID
I have two different SQL 2008 servers, I don't have permission to create a linked server in any of them. i created a trigger on server1.table1 to insert the same record to the remote server server2.table1 using OPENROWSET
i created a stored procedure to insert this record, and i have no issue when i execute the stored procedure. it insert the recored into the remote server.
The problem is when i call the stored procedure from trigger, i get an error message.
Stored Procedure: USE [DB1] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON
[Code] ....
When i try to insert a new description value in the table i got the following error message:
No row was updated the data in row 1 was not committed Error source .Net SqlClient Data provider. Error Message: the operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "(null)" returned message "The partner transaction manager has disabled its support for remote/network transaction.".
correct the errors entry or press ESC to cancel the change(s).
