Getting Inserted Data In Trigger
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
View Complete Forum Thread with Replies
Related Forum Messages:
Inserted Row Deletes After Trigger
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!
View Replies !
Put Inserted Value Into A Parameter In A Trigger
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 INSERT Source(srcfield1) VALUES ('A') go
View Replies !
How Do I Get A Trigger Just To Copy The Inserted Row.
HI I have a trigger which I want to copy any rows in the MSmerge_history into a archieve table. I am using this trigger: CREATE TRIGGER History_replication ON [dbo].[MSmerge_history] FOR INSERT AS INSERT [dbo].[MSmerge_history_archive] ( agent_id, runstatus, start_time, [time], duration, comments, delivery_time, delivery_rate, publisher_insertcount, publisher_updatecount, publisher_deletecount, publisher_conflictcount, subscriber_insertcount, subscriber_updatecount, subscriber_deletecount, subscriber_conflictcount, error_id, [timestamp] , updateable_row ) SELECT agent_id, runstatus, start_time, [time] , duration, comments, delivery_time, delivery_rate, publisher_insertcount, publisher_updatecount, publisher_deletecount, publisher_conflictcount, subscriber_insertcount, subscriber_updatecount, subscriber_deletecount, subscriber_conflictcount, error_id, [timestamp], 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
View Replies !
Using Inserted Table In Trigger
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? TIA
View Replies !
Trigger: Need To Use Inserted In Sp_executesql
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.
View Replies !
Trigger Inserted/Deleted
Hello, 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? Thanks, Steve
View Replies !
Any Column Updated / Inserted Trigger
Hi,I'm a SQL Server newbie, so I'd appreciate if someone would tell me ifthis is possible. I'm running SQL Server 2000 on Win2k ServerI have one table with a large number of columns. I have two pieces oflogic that I'd like to execute depending upon whether an insert or anupdate statement was executed on that table. I'd prefer thisexecution to occur from within a single trigger. If a row isinserted, then I would like to execute logic A. If ANY column in thetable is updated, then I'd like logic B to be executed.Is it possible to just determine if only "insert" or an "update"ocurred from within the a single Trigger, without specifying eachindividual column name? (I.E. not saying IF udpate(col1) orupdate(col2) or ect...) Is it possible to just perform a check on theprocess that occurred, irregardless of column? Like If INSERTED =TRUE then execute insert logic. If UPDATED = TRUE, then run theupdated logic. I would like for all of this code to be stored withinthe same trigger.If anyone can provide some sample code on how to do this, if at allpossible, I would be much appreciative.Thanks,-RigsPS I know I could do this with 2 seperate triggers, but I'm trying toavoid that.
View Replies !
Trigger, Inserted Pseudo Table?
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 SELECT @cProject = inserted.fkProjectNumber, @cComponent = inserted.fkComponentID, @iEntryID = inserted.EntryID ....... GO How do I use the inserted pseudo table? Mike B
View Replies !
Handling Inserted Table In Trigger
Hi There, I want know how to handle an Inserted Table in Trigger. In my case when i need to updated the inserted records in the database. I have written the code as follow. create trigger kc_message_queue_update on kc_message for update as declare@foldertype int, @folderid int, @msgid int select @folderid = folderid, @msgid = messageid from inserted begin select @foldertype = type from kc_folder where folderid = @folderid if (@foldertype = 1 or @foldertype = 0) update kc_message set custom_queueid = @folderid where messageid = @msgid end go This trigger works fine when i update a single record. When i do multiple record then it update only one record and rest of the record it puts null. Please give my a solution. Thanks, Senthil Kumar
View Replies !
Help! Trigger Using "inserted" Not Inserting Some Records.
Hello all, 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: EventName EventStatusID NumberofDays NumberofStores PreferredDate1 PleaseContactFlag EventStoreSelection EventClusterID 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 INSERT INTO mcweb2.dbo.mc_events (SystemID, EventID, AccountID, BillingContactID, EventName, EventStatusID, Coop, CoopSupplier, SamplesPerDay, BrochuresPerDay, AverageDailyMovement, SalesGoal, NumberofDays, NumberofHours, NumberofStores, WeekNumber, PreferredHourID, PreferredHourother, PreferredDate1, PreferredDate2, NewProductFlag, TVSupportFlag, RadioSupportFlag, FSISupportFlag, RollbackPricing, PleaseContactFlag, EventStoreSelection, EventClusterID, KitInformationID, KitDescription, KitOther, MCProgNum, rowguid) VALUES (@SystemID, @EventID, @AccountID, @BillingContactID, @EventName, @EventStatusID, @Coop, @CoopSupplier, @SamplesPerDay, @BrochuresPerDay, @AverageDailyMovement, @SalesGoal, @NumberofDays, @NumberofHours, @NumberofStores, @WeekNumber, @PreferredHourID, @PreferredHourother, @PreferredDate1, @PreferredDate2, @NewProductFlag, @TVSupportFlag, @RadioSupportFlag, @FSISupportFlag, @RollbackPricing, @PleaseContactFlag, @EventStoreSelection, @EventClusterID, @KitInformationID, @KitDescription, @KitOther, @MCProgNum, @rowguid) SET IDENTITY_INSERT mcweb2.dbo.mc_events OFF END TIA, Bruce Wexler Programmer/Analyst IT Department Mass Connections Ph: (562) 365-0200 x1091 Fx: (562) 365-0283 http://www.massconnections.com
View Replies !
How Is It Possible That Inserted And Deleted Are Both Empty In Trigger?
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'm doing that on Sql Server 2005.
View Replies !
Trigger- Get Row Number From Inserted/deleted
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. This SHOULD be simple... right?
View Replies !
Trigger To Indicate The Row Has Been Changed (updated Or Inserted)
Hi, 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. Can anyone help me with that, please? Cheers
View Replies !
Can Insert Trigger Update Record Being Inserted
I think a trigger is the way to go on this but let me try to explain the confusion. I have a table with an id field. Based upon this ID field I need to have a character stripped out of the ID and placed in another column on the same table/same record(Sorry, because of the development env I can't use a computer column). The question is how to do this with an insert trigger. My understanding is the trigger will fire on the insert, but the record isn't there yet. Is there a way to handle this? My code below does not error but also does not update? create TRIGGER test_Ins_tr ON table1 FOR update AS DECLARE @oid char(1) declare @actid char(10) SELECT @oid = SUBSTRING(right(col1,2),1,1), @actid = col1 FROM inserted update table1 set col2 = @oid where table1.col1 = @actid
View Replies !
Problem With My Trigger(inserted/deleted-tables)
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 !
View Replies !
Create A Trigger To Update A Row That's Been Inserted Or Updated
Hi Apologies if this is a silly question I have a basic table "Customer" which has Id Address1 Address2 Address3 Town County Postcode SearchData After I insert or Update a row in this table I need to update the SearchData column with UPPER(ADDRESS1) + UPPER(ADDRESS2) + UPPER(TOWN) + UPPER(POSTCODE) only for that Id I'm sure this is only a basic update but all the examples I can find are for inserting into other tables not the row itself. Regards David
View Replies !
Trigger Problem With "inserted" And "deleted" On Text Field
I use SQL Server 6.5 I have create a trigger on UPDATE for history. This history send the old value and the new value in a table to follow all the modifications. Those values are "text datatype". When I execute the trigger (When I make a modification in the table), the two values are the same (the new value). If somone have a idea ... CREATE TABLE dbo.HELPDB ( ALMID int NOT NULL , MEMO text NULL , FIRSTAID text NULL ) GO CREATE TRIGGER trg_UpdateHelpDB ON dbo.HELPDB FOR UPDATE AS DECLARE @Id integer DECLARE @alm integer DECLARE @user varchar(20) DECLARE @almname varchar(24) DECLARE @usergroupproprid integer DECLARE @usergroupproprname varchar(30) DECLARE @oldFirstAid varchar(255) DECLARE @newFirstAid varchar(255) if(UPDATE(FIRSTAID)) begin select @alm = ALMID, @oldFirstAid = CONVERT(varchar(255), FIRSTAID) from deleted select @newFirstAid = CONVERT(varchar(255), FIRSTAID) from inserted Exec AMX.dbo.SpGetNewId 'HISTALM', @Id OUTPUT select @user = "testuser" select @almname = "testalmname" select @usergroupproprname = "testusergroupproprname" INSERT HISTALM (ID, ALMID, DATETIME, USERLOG, STATUS, ALMNAME, USERGRPPROPR,USERGRPOWNER, EVENTTYPE, OLDVALUE, NEWVALUE) VALUES(@Id, @alm, getdate(), @user, "First Aid", @almname, @usergroupproprname, "/", 0, @oldFirstAid, @newFirstAid) END GO CREATE TABLE dbo.HISTALM ( ID int NOT NULL , ALMID int NOT NULL , DATETIME datetime NOT NULL , USERLOG varchar (30) NOT NULL , STATUS varchar (25) NOT NULL , ALMNAME varchar (24) NOT NULL , USERGRPPROPR varchar (30) NOT NULL , USERGRPOWNER varchar (30) NOT NULL , EVENTTYPE int NOT NULL , OLDVALUE varchar (255) NULL , NEWVALUE varchar (255) NULL ) GO
View Replies !
Text Column Is Null In Trigger Inserted Table
When I insert into a table with a text field, the insert trigger fires and then I try to access the Inserted.text field (to move it to another table), but it is NULL. I am running SQL 7.0 NT 4.0. This worked on 6.5, but not on 7.0. What is going wrong? Thanks. paulshafer
View Replies !
Order Of Records In The INSERTED/DELETED Tables In A Trigger
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. Thanks! CB
View Replies !
Trigger Problem, Comparing Deleted/inserted Not Working :(
Hello all, I have I trigger where I want to insert all _changed_ rows from the INSERTED table into a table called tempProducts. If I put this query inside my trigger, I selects exactly the rows I want: rows changed Code SnippetSELECT * FROM INSERTED EXCEPT SELECT * FROM DELETED I the current trigger I have Code SnippetINSERT INTO dbo.TempProducts (LBTyp, CountryOfOrigin) SELECT LBTyp, CountryOfOrigin FROM INSERTED but this inserts ALL updated rows, not only the changed ones. So I thought hey, I´ll just combine the two querys and the problem will be solved, like so: Code Snippet INSERT INTO dbo.TempProducts (LBTyp, CountryOfOrigin) SELECT LBTyp, CountryOfOrigin FROM (SELECT * FROM INSERTED EXCEPT SELECT * FROM DELETED) as Temp But for some reason, this won´t work! Why is this? What am I doing wrong?
View Replies !
Can I Debug/watch On The Trigger's INSERTED And DELETED Records/values?
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
View Replies !
How To Postpone Trigger Fire Until After Both Parent And Child Table Values Have Been Inserted
Hi all, 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? Thank you, John
View Replies !
Monitoring Inserted Data And Comparing Against Selected Data
I made ahuge load script in SQL Server 2000 as i load data from manytables(select some of each one collumns) into one single table and iwant to test the loaded data against the selected data to make surethat the loaded data is the same the selected datais there a code or tool to make this test or monitoring ?? pleaseurgent ....
View Replies !
Is Having A Trigger That Inserts A Row In Table 'A', When A Row In Same Table Is Inserted By ADo.Net Code?
I want to insert a row for a Global user in Table 'A' whenever ADO.Net code inserts a Local user row into same table. I recommended using a trigger to implement this functionality, but the DBA was against it, saying that stored proecedures should be used, since triggers are unreliable and slow down the system by placing unecessary locks on the table. Is this true OR the DBA is saying something wrong? My thinking is that Microsoft will never include triggers if they are unreliable and the DBA is just wanting to offload the extra DBA task of triggers to the programmer so that a stored procedure is getting called, so he has less headache on his hands.Thanks
View Replies !
Q: Trigger - Reference To Fields In &"inserted&" ?
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 end
View Replies !
Trigger- Dump 'inserted' Table To Temp Table
I want to pass the 'inserted' table from a trigger into an SP, I think I need to do this by dumping inserted table into a temporary table and passing the temp table. However, I need to do this for many tables, and don't want to list all the column names for each table/trigger (maintenance nightmare). Can I dump the 'inserted' table to a temp table WITHOUT specifying the column names?
View Replies !
How To Use Table &&"inserted&&" Within Exec In A Trigger?
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
View Replies !
How WCF Can Get New Inserted SQL Data ?
dear all, I have a WCF service which is host in a console application for the time beeing. This service provide methods for retriving history data when request. So far so good. My WCF service need also to know when a particular table (ALARMS tabel ) gets updated with DELETE, INSERT, OR UPDATE. This because my client application (WinForm) need to refresh a datagrid binding to that ALARMS table. In other words my WCF servcie would send a callback event to my client when it as been notice for a change.. But my problem is how my WCF service can be notify from an update in my SQL table ? I have tried SQLDependency class, but I give up was not working properly...and hard to know the xact context you are runing on. Was thinking also of having a timer whcih pool every 1s by calling my tabel store procedure and verifiy is somerow ha changed.... What to do, how to do, what is the best way and thred safe method Thnaks for help and advise regards serge
View Replies !
INSERT INTO - Data Is Not Inserted
hi thereCreated sproc - it stops dead in the first lineWhy ????Thanks in advanceCREATE PROCEDURE [dbo].[test] ASinsert into timesheet.dbo.table1 (RE_Code, PR_Code, AC_Code, WE_Date,SAT, SUN, MON, TUE, WED, THU, FRI, NOTES, GENERAL, PO_Number,WWL_Number, CN_Number)SELECT RE_Code, PR_Code, AC_Code, WE_Date, SAT, SUN, MON, TUE,WED, THU, FRI, NOTES, GENERAL, PO_Number, WWL_Number, CN_NumberFROM dbo.WWL_TimeSheetsWHERE (RE_Code = 'akram.i') AND (WE_Date = CONVERT(DATETIME,'1999-12-03 00:00:00', 102))GO
View Replies !
Access Inserted Data
i have a oledb destination in my data flow pointing to table ABC and an error output if the insert failed..follow the error output, i have a lookup on table ABC which doesn't seem to work..is it possible to access new data in table ABC follow the error output? thanks
View Replies !
Prevent Data Being Inserted Twice
I have a table with 3 columns: ID, Status, DateTime. I created a stored procedure to insert a staus value for each ID. This will run every hour. The DateTime stores the time, date when the Status was inserted. If the procedure was to be run a second time in hour window I do not want any Status to be inserted. Note: that I cannot rely on the procedure being run at exactly the right time - if it was scheduled to run on the hour (i.e at 1:00, 2:00, 3 :00 etc) but didn't run until 1:20 it sould still be able to run at 2:00. Does anyone know if there is anyway I can gaurd against this?
View Replies !
How To Get The ID Of An Inserted Data In A Stored Procedure
hi iam working for a stored procedure where i am inserting data for a table in a database and after inserting i must get the ID in the same procedure.later i want to insert that output ID into another table inthe same stored procedure. for example: alter procedure [dbo].[AddDetails] ( @IndustryName nvarchar(50), @CompanyName nvarchar(50), @PlantName nvarchar(50), @Address nvarchar(100), @Createdby int, @CreatedOn datetime ) as begin insert into Industry(Industry_Name,Creadted_by,Creadted_On) OUTPUT inserted.Ind_ID_PK values(@IndustryName,@Createdby,@CreatedOn) insert into Company(Company_Name,Company_Address,Created_by,Created_On,Ind_ID_FK) OUTPUT inserted.Cmp_ID_PK values(@CompanyName,@Address,@Createdby,@CreatedOn) insert into Plant(Plant_Name,Created_by,Creadted_On,Ind_ID_FK,Cmp_ID_FK)values(@PlantName,@Createdby,@CreatedOn,@intReturnValueInd,@intReturnValueComp) end Here iam getting the output ID of the inserted data as OUTPUT inserted.Ind_ID_PK and later i want to insert this to the company table into Ind_ID_FK field.how can i do this. Please help me, i need the solution soon.
View Replies !
Converting Data To Be Inserted Into A Database
Hi,I am using web matrix, and I am trying to insert a data into a MSDE database. I have used webmatrix to generate the update code, and it is executed when a button is pressed on the web page. but when the code is executed I get the error:Syntax error converting the varchar value 'txtAmountSold.text' to a column of data type int.So I added the following code to try to convert the data, but i am still getting the same error, with txtAmountSold.text replaced with "test"dim test as integer test = Convert.ToInt32(txtAmountSold.text)Here is the whole of the function I am using:Function AddItemToStock() As Integer dim test as integer test = Convert.ToInt32(txtAmountSold.text) Dim connectionString As String = "server='(local)Matrix'; trusted_connection=true; database='HawkinsComputers'" Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString) Dim queryString As String = "INSERT INTO [stock] ([Catagory], [Type], [Name], [Manufacturer], [Price], [Weight"& _ "], [Description], [image], [OnOffer], [OfferPr"& _ "ice], [OfferDescription], [AmountInStock], [AmountOnOrder], [AmountSold]) VALUES ('CatList.SelectedItem.text', 'txtType.text', 'txtname.text', 'txtmanufacturer.text'"& _ ", convert(money,'txtPrice.text'), 'txtWeight.text', 'txtDescription.text', 'txtimage.text', 'txtOnOffer"& _ ".text', convert(money,'txtOfferPrice.text'), 'txtOfferDescrip"& _ "tion.text', 'txtAmountInStock.text', 'txtAmountOnOrder.text', 'test')" Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand dbCommand.CommandText = queryString dbCommand.Connection = dbConnection Dim rowsAffected As Integer = 0 dbConnection.Open Try rowsAffected = dbCommand.ExecuteNonQuery Finally dbConnection.Close End Try Return rowsAffected End FunctionAny help in solving this problem would be greatly appreciated, as I am really stuck for where to go next.
View Replies !
Check Inserted Data In A SQL Database
Hi im having problems as im new to ASP.NET C# i have created a button to add details into a SQL database but i want to check the details before i insert the new values from the textboxes can anyone help....... this is what i have to insert into the database........i just want some help to compare the user name eg... if user name exists a message will appear telling the user to change a different user name Thanks private void Button1_Click(object sender, System.EventArgs e) { string connectionString = "server='(local)'; trusted_connection=true; database='tester'"; //System.Data.IDbConnection conn = new System.Data.SqlClient.SqlConnection(connectionString); System.Data.IDbConnection conn = new System.Data.SqlClient.SqlConnection(connectionString); conn.Open(); string commandString = "INSERT INTO Users (UserName, Password) " + "Values(@UserName, @Password)"; //SqlCommand dbCommand = new SqlCommand (commandString, dbconn); System.Data.IDbCommand dbCommand = new System.Data.SqlClient.SqlCommand(); //System.Data.SqlClient.SqlCommand myCmd = new System.Data.SqlClient.SqlCommand(queryString, conn); dbCommand.CommandText = commandString; dbCommand.Connection = conn; SqlParameter unParam = new SqlParameter ("@UserName", SqlDbType.NVarChar, 60); unParam.Value = txtUser.Text; dbCommand.Parameters.Add(unParam); SqlParameter paParam = new SqlParameter ("@Password", SqlDbType.NVarChar, 60); paParam.Value = txtPassword.Text; dbCommand.Parameters.Add(paParam); dbCommand.ExecuteNonQuery(); conn.Close(); Response.Redirect ("WebForm1.aspx"); }
View Replies !
Clipping Data Inserted Into NText Field
I'm having a major problem. I'm executing an sql statement to insert data into an nText field. It's clipping the text though. I haven't seen any patterns yet. Example "Welcome to the show.", it would save "Welcome to t". And it's not all the time. Please Help. Thanks for your time, Randy
View Replies !
Incorrect Data Is Inserted Into The SQL Table From OLEDB Command
I have an OLEDB command in a package that inserts the data into two tables. When I run the package, the data is getting inserted as divided by 100 of original data for derived columns. For example: Say col1 is my input column from flat file with value 1000. I am dividing it by 100 in Derived column and then inserting into the table. So the value 100 should be inserted into the table. But it is not so, the value 1 is getting inserted. Two Tables involved here have referential integrity constraints between them. SQL Script for the operation I am doing looks like the one just below INSERT INTO PrimaryKeyTable(ID, FirstName) VALUES (?,?) If @@rowcount=1 BEGIN DECLARE @MaxID as int SELECT @MaxID =max(AutoID) FROM dbo.PrimaryKeyTable --AutoIncremented column INSERT INTO [ForeignKeyTable] ( [MaxID] ,[Cost] ,[MarkDownDollars] ,[VersionCode] ) VALUES(@MaxID,?,?,'act') END But this script did not work in OLEDB commandL So I wrote the below script for which I am facing the problem mentioned INSERT INTO PrimaryKeyTable(ID, FirstName) VALUES (?,?) If @@rowcount=1 BEGIN DECLARE @Cost AS money SET @Cost=? DECLARE @MarkDownDollars AS money SET @MarkDownDollars=? DECLARE @MaxID as int SELECT @MaxID =max(AutoID) FROM dbo.PrimaryKeyTable INSERT INTO [ForeignKeyTable] ( [MaxID] ,[Cost] ,[MarkDownDollars] ,[VersionCode] ) VALUES(@MaxID,@Cost,@MarkDownDollars,'act') END
View Replies !
Using Inserted / Deleted Tables With Text / NText / Image Data Type
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.
View Replies !
Can't Access &&"inserted&&" Table From Trigger; Msg 4104 &&"The Multi-part Identifier &&"...&&" Could Not Be Bound.&&"
I'm a newbie have trouble using the "inserted" table in a trigger. When I run these SQL statements:CREATE DATABASE foobarGOUSE foobar GOCREATE TABLE foo ( fooID int IDENTITY (1, 1) NOT NULL, lastUpdated datetime, lastValue int, PRIMARY KEY(fooID))GOCREATE TABLE bar ( barID int IDENTITY (1, 1) NOT NULL, fooID int NOT NULL, [value] int NOT NULL, updated datetime NOT NULL DEFAULT (getdate()), primary key(barID), foreign key(fooID) references foo (fooID))GOCREATE TRIGGER onInsertBarUpdateFoo ON Bar FOR INSERTAS UPDATE Foo SET lastUpdated = inserted.updated, lastValue = inserted.[Value] WHERE foo.fooID = inserted.fooIDGO I get the error message: Msg 4104, Level 16, State 1, Procedure onInsertBarUpdateFoo, Line 4 The multi-part identifier "inserted.fooID" could not be bound. I can get the trigger to work fine as long as I don't reference "inserted". What am I missing? I'm using Microsoft SQL Server Management Studio Express 9.00.2047.00 and SQL Express 9.0.1399 Thanks in advance for your help... Larry
View Replies !
INSERT INTO - Data Is Not Inserted - Using #temp Table To Populate Actual Table
Hi thereApplication : Access v2K/SQL 2KJest : Using sproc to append records into SQL tableJest sproc :1.Can have more than 1 record - so using ';' to separate each linefrom each other.2.Example of data'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;'HARLEY.I',03004,'A000-AA00',2003-08-29,0,0,7.5,7.5,7.5,7.5,7.0,'Notes','General',1,2,3 ;3.Problem - gets to lineBEGIN TRAN <---------- skipsrestINSERT INTO timesheet.dbo.table14.Checked permissions for table + sproc - okWhat am I doing wrong ?Any comments most helpful......CREATE PROCEDURE [dbo].[procTimesheetInsert_Testing](@TimesheetDetails varchar(5000) = NULL,@RetCode int = NULL OUTPUT,@RetMsg varchar(100) = NULL OUTPUT,@TimesheetID int = NULL OUTPUT)WITH RECOMPILEASSET NOCOUNT ONDECLARE @SQLBase varchar(8000), @SQLBase1 varchar(8000)DECLARE @SQLComplete varchar(8000) ,@SQLComplete1 varchar(8000)DECLARE @TimesheetCount int, @TimesheetCount1 intDECLARE @TS_LastEdit smalldatetimeDECLARE @Last_Editby smalldatetimeDECLARE @User_Confirm bitDECLARE @User_Confirm_Date smalldatetimeDECLARE @DetailCount intDECLARE @Error int/* Validate input parameters. Assume success. */SELECT @RetCode = 1, @RetMsg = ''IF @TimesheetDetails IS NULLSELECT @RetCode = 0,@RetMsg = @RetMsg +'Timesheet line item(s) required.' + CHAR(13) + CHAR(10)/* Create a temp table parse out each Timesheet detail from inputparameter string,count number of detail records and create SQL statement toinsert detail records into the temp table. */CREATE TABLE #tmpTimesheetDetails(RE_Code varchar(50),PR_Code varchar(50),AC_Code varchar(50),WE_Date smalldatetime,SAT REAL DEFAULT 0,SUN REAL DEFAULT 0,MON REAL DEFAULT 0,TUE REAL DEFAULT 0,WED REAL DEFAULT 0,THU REAL DEFAULT 0,FRI REAL DEFAULT 0,Notes varchar(255),General varchar(50),PO_Number REAL,WWL_Number REAL,CN_Number REAL)SELECT @SQLBase ='INSERT INTO#tmpTimesheetDetails(RE_Code,PR_Code,AC_Code,WE_Da te,SAT,SUN,MON,TUE,WED,THU,FRI,Notes,General,PO_Nu mber,WWL_Number,CN_Number)VALUES ( 'SELECT @TimesheetCount=0WHILE LEN( @TimesheetDetails) > 1BEGINSELECT @SQLComplete = @SQLBase + LEFT( @TimesheetDetails,Charindex(';', @TimesheetDetails) -1) + ')'EXEC(@SQLComplete)SELECT @TimesheetCount = @TimesheetCount + 1SELECT @TimesheetDetails = RIGHT( @TimesheetDetails, Len(@TimesheetDetails)-Charindex(';', @TimesheetDetails))ENDIF (SELECT Count(*) FROM #tmpTimesheetDetails) <> @TimesheetCountSELECT @RetCode = 0, @RetMsg = @RetMsg + 'Timesheet Detailscouldn''t be saved.' + CHAR(13) + CHAR(10)-- If validation failed, exit procIF @RetCode = 0RETURN-- If validation ok, continueSELECT @RetMsg = @RetMsg + 'Timesheet Details ok.' + CHAR(13) +CHAR(10)/* RETURN*/-- Start transaction by inserting into Timesheet tableBEGIN TRANINSERT INTO timesheet.dbo.table1select RE_Code,PR_Code,AC_Code,WE_Date,SAT,SUN,MON,TUE,WE D,THU,FRI,Notes,General,PO_Number,WWL_Number,CN_Nu mberFROM #tmpTimesheetDetails-- Check if insert succeeded. If so, get ID.IF @@ROWCOUNT = 1SELECT @TimesheetID = @@IDENTITYELSESELECT @TimesheetID = 0,@RetCode = 0,@RetMsg = 'Insertion of new Timesheet failed.'-- If order is not inserted, rollback and exitIF @RetCode = 0BEGINROLLBACK TRAN-- RETURNEND--RETURNSELECT @Error =@@errorprint ''print "The value of @error is " + convert (varchar, @error)returnGO
View Replies !
Firing A Trigger When A User Updates Data But Not When A Stored Procedure Updates Data
I have a project that consists of a SQL db with an Access front end as the user interface. Here is the structure of the table on which this question is based: Code Block create table #IncomeAndExpenseData ( recordID nvarchar(5)NOT NULL, itemID int NOT NULL, itemvalue decimal(18, 2) NULL, monthitemvalue decimal(18, 2) NULL ) The itemvalue field is where the user enters his/her numbers via Access. There is an IncomeAndExpenseCodes table as well which holds item information, including the itemID and entry unit of measure. Some itemIDs have an entry unit of measure of $/mo, while others are entered in terms of $/yr, others in %/yr. For itemvalues of itemIDs with entry units of measure that are not $/mo a stored procedure performs calculations which converts them into numbers that has a unit of measure of $/mo and updates IncomeAndExpenseData putting these numbers in the monthitemvalue field. This stored procedure is written to only calculate values for monthitemvalue fields which are null in order to avoid recalculating every single row in the table. If the user edits the itemvalue field there is a trigger on IncomeAndExpenseData which sets the monthitemvalue to null so the stored procedure recalculates the monthitemvalue for the changed rows. However, it appears this trigger is also setting monthitemvalue to null after the stored procedure updates the IncomeAndExpenseData table with the recalculated monthitemvalues, thus wiping out the answers. How do I write a trigger that sets the monthitemvalue to null only when the user edits the itemvalue field, not when the stored procedure puts the recalculated monthitemvalue into the IncomeAndExpenseData table?
View Replies !
.Net Trigger, Reading All The Data
I have a trigger written in C# which I have added to the insert event on a table, however, when testing it generates a "System.Data.SQLClient.SqlException; Cannot use text, ntext, or image columns in the 'inserted' and 'deleted' tables" My code is attempting to read all the column names & the data contained in them as the record is created, so a solution that allows me to read all the data from each column is what I am after. Code Extract: public static void splTrigger() { SqlTriggerContext triggContext = SqlContext.TriggerContext; // string userName, realName; SqlConnection connection = new SqlConnection("context connection = true"); connection.Open(); SqlCommand command = connection.CreateCommand(); SqlDataReader reader; string data = ""; switch (triggContext.TriggerAction) { case TriggerAction.Insert: command.CommandText = "SELECT * from " + "inserted"; reader = command.ExecuteReader(); //userName = (string)reader[0]; //realName = (string)reader[1]; // prepare data as name value pairs for (int i = 0; i < reader.FieldCount; i++) { data = data + reader.GetName(i) + ":" + (string)reader[ i ] + " "; } break; ... }}
View Replies !
|
TRACKER
