Insert / Update Triggers Not Working After Upgrade?
Mar 11, 2014
I recently moved a database from a SQL server 2005 box to new server running SQL server 2012. The update/insert triggers that were working on the tables to handle referential integrity checking are no longer working. Running the same database on SQL Server 2008 and everything works.
Here is one of the trigger that throws the error 44446
USE
[M2Data]
GO
/****** Object:
Trigger [dbo].[tblContacts_UTrig] Script Date: 3/11/2014 9:07:13 AM ******/
SET
ANSI_NULLS ON
[code]....
I have verified that there is a matching key in tblCompanys. Also, when I run a query to update tblContacts the error message appears but the update does take. If I try and edit the row directly by selecting edit top 200 rows - the error message appears and the update does NOT take.
These triggers were probably added to the database during an upsize from MS Access to SQL Server 7 way back. What am I missing - is it something in the syntax?
I'm working on inserting data into a table in a database. The table has two separate triggers, one for insert and one for update (I don't like it this way, but that's how it's been for years). When there is a normal insert, done via a program, it looks like the triggers work fine. When I run an insert manually via a script, the first insert trigger will run, but the update trigger will fail. I narrowed down the issue to a root cause.
This root issue is due to both triggers using the same temporary table name. When the second trigger runs, there's an error stating that a few columns don't exist. I went to my test server and test db and changed the update trigger so that the temporary table is different than the insert trigger temporary table, the triggers work fine. The weird thing is that if the temporary table already exists, when the second trigger tries to create the temporary table, I would expect it to fail and say that it already exists.I'm probably just going to update the trigger tonight and change the temporary table name.
SQL Server 2000 : I have a series of tables which all have the samestructure. When any of these tables are modified I need to syncrhoniseall of those modifications with one other table wich is a sort of mergeof the individual tables with one extra column.For most of these tables this is not a problem. The problem arriveswhen one of the tables has an ntext column which obviously can not beused in an update or insert trigger.Here's an example of one of them:CREATE TABLE tblImages(ID INT IDENTITY(1,1) PRIMARY KEY,Inventory nvarchar(8) NOT NULL,Coll nvarchar(8) NOT NULL,ImageFile nvarchar(128) NOT NULL,ImageNotes ntext NULL,TS timestamp NULLCONSTRAINT U_Images UNIQUE NONCLUSTERED (ItemCode, Inventory, Coll,ImageFile)I then had created an update trigger which looked like this:CREATE TRIGGER COLLNAME_UTRIGGER ON COLLNAME_ImagesFOR UPDATEASBEGINUPDATE tblImages SETInventory = inserted.Inventory,Coll = 'COLLNAME',ImageFile = inserted.ImageFileName,FROM inserted INNER JOIN tblImages ON inserted.ItemCode =tblImages.ItemCode ANDinserted.Invventory = tblImages.Invventory AND tblImages.Coll ='COLLNAME' ANDinserted.ImageFileName = tblImages.ImageFileUPDATE tblImagesSET ImageNotes=inserted.NotesFROM inserted INNER JOIN tblImages ON inserted.ItemCode =tblImages.ItemCode ANDinserted.Inventory= tblImages.Inventory AND tblImages.Coll ='COLLNAME' ANDinserted.ImageFileName = tblImages.ImageFileEND " & vbCrLf)The first update in my trigger, be it an update or insert trigger,works fine. It crashes with the "Cannot use text, ntext or imagecolumns in the 'inserted' or 'deleted' tables." error in the secondpart.I have read various messages through the Internet on this and severalof them reference using INSTEAD OF triggers and views. I have neverused those before as this is my first work with SQL 2000. None of theexamples of INSTEAD OF triggers I have seen yet use the actual insertedtables and I haven't quite understood how to use them correctly.Can someone help me with the basic syntax as this trigger is one ofseveral that I am going to have to get working.Thank you in advance for any help, assistance, suggestions or"direction pointing" you may provide.
I want to be able to duplicate every single record that is inserted or updated in a particular table to another table, but not the delete. Is the best way to set-up a trigger? If so can anyone provide me with an example of how to do this? Also could you just duplicate certain columns in the row I would you have to do all columns?
I've amended a table to include some extra columns to track when changes are made. Next step is to amend the stored procedure that updates that table when the changes are made.
I amended an existing stored proc to include CreateTS, CreateID, ModifyTS, ModifyID. Unfortunately, the INSERT and UPDATE aren't working for the new columns.
Am fairly new to this, so not sure why it's not working? Code is below:
DECLARE @ThisBSB VarChar(6) DECLARE @intCount int DECLARE @intInserted int DECLARE @intUpdated int
SET @intInserted = 0 SET @intUpdated = 0
-- fields from New Table
DECLARE curBSB CURSOR FOR SELECT Replace(bsbnumber,'-','') FROM ztblBSBText (nolock)
OPEN curBSB
FETCH NEXT FROM curBSB INTO @ThisBSB
WHILE @@FETCH_STATUS = 0 BEGIN
--Print @ThisBSB
-- See if this BSB Already Exists SELECT @Intcount = Count(*) FROM tblBankBSB (nolock) WHERE BSBcode = @ThisBSB
IF @intCount = 0 BEGIN
-- Insert New Record --Print 'Insert: ' + @ThisBSB INSERT INTO tblBankBSB ([BSBCode] ,[BankID] ,[BranchNumber] ,[BranchName] ,[CountryID] ,[Address] ,[Suburb] ,[StateID] ,[StateCode] ,[State] ,[PostcodeID] ,[Postcode] ,[StatusID] ,[TransferedToBSB] ,[CreateID] ,[CreateTS] ,[ModifyID] ,[ModifyTS]) SELECT @ThisBSB ,tblBank.BankID ,Cast(Right(bsbnumber,3) AS Int) ,ztblBSBText.BSBName ,1 ,ztblBSBText.Address ,ztblBSBText.Suburb ,tblState.StateId ,Null ,ztblBSBText.State ,Null ,ztblBSBText.Postcode ,1 ,Null ,Null ,Null ,@UserContactID ,getDate() FROM ztblBSBText INNER JOIN tblBank (nolock) on ztblBSBText.Mnemonic = tblBank.BankCode INNER JOIN tblState (nolock) on ztblBSBText.State = tblState.State WHERE tblState.StatusID = 1 AND tblState.CountryID = 1 AND Replace(bsbnumber,'-','') = @ThisBSB
SET @intInserted = @intInserted + 1 END
ELSE BEGIN
-- See If Closed since last time this was run, and if so, update SELECT @intCount = Count(*) FROM ztblBSBText INNER JOIN tblBankBSB (nolock) ON Replace(ztblBSBText.bsbnumber,'-','') = tblBankBSB.BSBCode WHERE Replace(bsbnumber,'-','') = @ThisBSB AND ztblBSBText.BSBName = 'Closed' AND tblBankBSB.BranchName Not Like '%Closed%'
IF @intCount > 0 BEGIN
--Print 'Update: ' + @ThisBSB UPDATE tblBankBSB SET tblBankBSB.StatusID = 0 ,tblBankBSB.BranchName = tblBankBSB.BranchName + ' - Closed' ,tblBankBSB.TransferedToBSB = (SELECT replace(substring(address, 14,7),'-','') FROM ztblBSBText WHERE Replace(ztblBSBText.bsbnumber,'-','') = @ThisBSB) ,tblBankBSB.ModifyID = @UserContactID ,tblBankBSB.ModifyTS = getDate() WHERE BSBCode = @ThisBSB
SET @intUpdated = @intUpdated + 1 END
END
FETCH NEXT FROM curBSB INTO @ThisBSB
END
CLOSE curBSB DEALLOCATE curBSB
_____________________________ "Nihil est incertius volgo." - Cicero
Dim cn As New System.Data.SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("LocalSqlServer").ToString())
cn.Open()
Dim adapter1 As New System.Data.SqlClient.SqlDataAdapter()
adapter1.SelectCommand = New Data.SqlClient.SqlCommand("update aspnet_Membership_BasicAccess.Products set id = '" & textid.Text & "', name = '" & textname.Text & "', price = '" & textprice.Text & "', description = '" & textdescription.Text & "', count = '" & textcount.Text & "', pictureadd = '" & textpictureadd.Text & "', artist = '" &textartist.Text & "', catergory = '" & textcategory.text & "' where id = " & Request.Item("id") & ";", cn)
cn.Close()
Response.Redirect("database.aspx")
it posts and the page loads but the data is still the same in my datagrid. what could be wrong with this simple statement... i've tried testing the statement above with constant values of the correct type but i don't think that matters because the SqlCommand() accepts a string only anyways.. doesn't it?
Hi,I am trying to implement a trigger that offers the functionality of the following stored procedure: SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[SynchronizeManagers]ASBEGINSET NOCOUNT ONDeclare @Err intSET @Err = 0BEGIN TRANSACTIONINSERT INTO DB2.dbo.tbl_ProjectManager (FName, LName)SELECT Firstname, LastnameFROM DB1.dbo.vwUserDetails AWHERE NOT EXISTS ( SELECT * FROM DB2.dbo.tbl_ProjectManager P WHERE P.Fname = A.Firstname AND P.LName = A.Lastname )SET @Err = @@ERRORUPDATE DB2.dbo.tbl_ProjectManagerSET DB2.dbo.tbl_ProjectManager.Inactive = 1WHERE NOT EXISTS (SELECT * FROM DB1.dbo.vwUserDetails A WHERE A.Firstname = DB2.dbo.tbl_ProjectManager.FName AND A.Lastname = DB2.dbo.tbl_ProjectManager.LName)SET @Err = @@ERROR
IF @Err <> 0BEGINROLLBACK TRANSACTION RETURN @ErrENDCOMMIT TRANSACTIONSET NOCOUNT OFFEND Basically, I need the trigger to run whenever a new name is added to or deleted from DB1.I've read up on triggers but still am not sure how exactly to use them. This problem is mainly because I don't have direct access to the table that holds the names in DB1. All I have access to is a View (called vwUserDetails).Any help is appreciated. Thanks.
I have a DTS package that is importing a fixed length file.After installing SP3a (on PERSONAL & DEVELOPER edition) it will notallow me to assign more than 125 fixed length column positions.After hitting the Finish & OK button it just wipes out the columnsettings.I've reinstalled both Personal & Developer edition. DTS will allowmore than 125 prior to the service pack.
The Folowing code is not working anymore. (500 error)
Set objRS = strSQL1.Execute strSQL1 = "SELECT * FROM BannerRotor where BannerID=" & cstr(BannerID) objRS.Open strSQL1, objConn , 2 , 3 , adCmdText If not (objRS.BOF and objRS.EOF) Then objRS.Fields("Exposures").Value =objRS.Fields("Exposures").Value + 1 objRS.update End If objRS.Close
The .execute Method works fine
strSQL1 = "UPDATE BannerRotor SET Exposures=Exposures+1 WHERE BannerID=" & cstr(BannerID) objConn.Execute strSQL1
I am writing a pgm that attaches to a SQL Server database. I have an Add stored procedure and an Update stored procedure. The two are almost identical, except for a couple parameters. However, the Add function works and the Update does not. Can anyone see why? I can't seem to find what the problem is...
This was my test:
Dim cmd As New SqlCommand("pContact_Update", cn) 'Dim cmd As New SqlCommand("pContact_Add", cn)
Catch ex As Exception Label1.Text = ex.Message End Try
When I use the Add procedure, a record is added correctly and I receive the "done" message. When I use the Update procedure, the record is not updated, but I still receive the "done" message.
I have looked at the stored procedures and the syntax is correct according to SQL Server.
I had a number of jobs which were backing up system and user databases under SQL 7 using SQL ServerAgent. Over the weekend I upgraded SQL 7 to SQL 2K.
All backups work except for one, which gets the following error message: Executed as user: SANDIEGOSvcSQLServer. sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The step failed.
As best as I know SQLSTATE 42000 is an ODBC message indicating a syntax violation or an access violation. I have recreated the maintenance plan in an attempt to rule out a syntax violation. During the upgrade process I did not change the account under which SQL Server Agent runs, so it seems like it should not be an access violation.
Any ideas would be appreciated!!!
Thanks Gary Andrews Andrews_gary_w@solarturbines.com
Prior to our move to 2005...permissions were granted to developers by adding them to the following fixed database roles...db_ddladmin, db_datareader, db_datawriter, and db_securityadmin. They created their objects using 'dbo' as the owner.
After upgrading to 2005, suddently they are having difficulty accessing their objects with this same security. Do they need permissions on the dbo schema?
Hello, what i want is simple. This is a simple forum, it has several topics (that the users can create), when a user create a topic, its stored in forum_topics. The user can then view the topic and post a response that is store in forum_answer, the user can also add this to his favorite list, forum_favorites is simple, contains a TopicID that refers to the topic, a username of the user that has the topic on his favorite list and a auto increment id to be able to delete specified topic favorites. Now my question is: when a user posts a answer to Topic X, i want a predefined message to be sent to post_inbox for all the users that has Topic X in their favorite list. How can i get MS SQL 2005 to get all the users from Topic X and then loop thru them and insert a new post into post_inbox?
I've been reading the docs and playing around, but I'm still notgetting the difference. For instance,create table a(i int check(i>0))create table a_src(i int)gocreate unique index ai on a(i) with IGNORE_DUP_KEYgoinsert into a_src values(1)insert into a_src values(1)insert into a_src values(2)--insert into a_src values(-1)gocreate trigger a4ins on afor insertasselect * from insertedgocreate trigger afterins on aafter insertasselect * from insertedgoinsert into a select * from a_srcgodrop table adrop table a_srcI'm gettingi-----------12(2 row(s) affected)Server: Msg 3604, Level 16, State 1, Procedure a4ins, Line 4Duplicate key was ignored.i-----------12(2 row(s) affected)even the inserted quasi tables are identical.If I uncomment insert into a_src values(-1), I'm gettingServer: Msg 547, Level 16, State 1, Line 1INSERT statement conflicted with COLUMN CHECK constraint'CK__a__i__58FC18A6'. The conflict occurred in database 'ABC_1COMPEE',table 'a', column 'i'.The statement has been terminated.without any output from either trigger.So,in which situations will FOR INSERT be useful while AFTER INSERT won'tdo?in which situations will AFTER INSERT be useful while FOR INSERT won'tdo?
This morning I attempted to connect to my local copy of SQL Server (2012) (Win 7) using Windows Authentication. I was unable to do so and per the error log it was due to error 18456 (invalid credentials).
Yesterday I changed my windows password. Is it possible for this to be the cause?
FYI, following is from the error logs:
2015-04-27 10:10:32.98 Backup BACKUP DATABASE successfully processed 0 pages in 0.451 seconds (0.000 MB/sec).2015-04-28 00:00:41.90 spid18s This instance of SQL Server has been using a process ID of 2096 since 4/21/2015 9:08:05 AM (local) 4/21/2015 1:08:05 PM (UTC). This is an informational message only; no user action is required.2015-04-28 09:58:51.16 Logon Error: 18456, Severity: 14, State: 38.2015-04-28 09:58:51.16 Logon Login failed for user 'NT AUTHORITYSYSTEM'. Reason: Failed to open the explicitly specified database 'model'. [CLIENT: xx.xx.xxx.xxx]2015-04-28 17:09:59.87 Server SQL Server is terminating because of a system shutdown. This is an informational message only. No user action is required.
The logs showed the error occurred yesterday (4/28) which is about when I changed my password. The 17:09 log (last one) is when I shut down my computer. Note that I was logged into SQL Server when I changed my Windows password and worked with it several times through out the day.
Also, per the configuration manager SQL Server (MSSQLSERVER) (and all other services) is stopped and I was unable to start it ("The request failed or the service did not respond in a timely fashion).
I upgraded to Sql Server Express 2005 w/Advanced Services, chose to install full-text indexing, rebooted, but appear to be unable to set up full-text indexing for a column. After attaching the old mdf file and opening any table in either Management Studio Express or Visual C# Express, I don't get anything but a disabled (Is Full-text Indexed) property for the fields I want to set up. I have verified that FullText Search service is running, and have tried restarting the service. I have also checked that full-text indexing is enabled on the database.
I just feel like I am missing some simple step. Any info would be welcome.
I am trying to understand how to use instead of trigger using an update.
Here are a few things that I need clarified.
- When using an 'instead of Update' trigger is this replacing any other Update that is happening to the Table.
- Or, is the occurence of the trigger happening after the update?
- Also, I have seen some syntax where people use: Update table set field1 = inserted.field1 from deleted, inserted, table where deleted.field1 = ....
Does this mean that when using the 'instead of' option it keeps track of what is being deleted and what is being inserted by using such things as deleted.field1 or inserted.field1?
What I need to do is check that the previous value in a particular field is null before updating it. I don't want to update a particular field that already contains a value in it.
Hi there I' d like to make a trigger which will come with some kind of message ( maybe jscript?) or printout, each time I insert new client into the Client table. Clent table has ClientID, ClientName, Address, EMail, and ContactPerson. Please help
I have written an Insert Trigger to examine newly inserted records and set some values. However, each time a record is inserted, all records are checked. How can I make the trigger work only on newly inserted records?
What would be the best way to handle different updates for a table, multiple triggers, or just one large triggger? I am not worried about their order of firing, just that they fire
I try to use trigger to see if Component table update at same time update the AssemblySubcomponent AssemblyID
but it say
Msg 4104, Level 16, State 1, Procedure trigupdateSubcomponentID, Line 6 The multi-part identifier "a.SubcomponentID" could not be bound.
the code look like
create trigger trigupdateAssemblyID on Component for update, insert as begin if update (ComponentID) update AssemblySubcomponent set a.AssemblyID= i.ComponentD from inserted i join AssemblySubcomponent a on a.AssemblyID =i.ComponentID
I have a situation where I need to create an insert trigger on table a which will create a corresponding record in table b. However before I insert the record i must obtain the max value for the record in table b and increment it by one. I have all this working. My question is if I just put a begin and commit with this statement is there a chance that when 2 users insert at the same time the max value may be incorrect say for instance
CREATE TRIGGER tr_cms_prov_ins ON provider FOR INSERT as declare @ndentPrid char(3), @nxtgenPrid char(7), @fname varchar(40), @lname varchar(40) begin tran select @ndentPrid = max(provider_id) from providerdnt if @ndentPrid is null set @ndentPrid = 1 else set @ndentPrid = @ndentPrid + 1
insert into dental..provider (provider_id, first_name, last_name, collections_go_to) select @ndentPrid, first_name, last_name','YYYYYYYYYYYYYYYNNNNN' from inserted commit tran
Will this do it or do I need to enforce some type of locking to handle the max value. There are no inserts into table b directly only by the trigger insert on table a
Hello all, I'm in the process of debugging a trigger i've written (which hasn't been any fun) and i have a question.
records for the table with the trigger are only inserted from a SP using an INSERT INTO ... SELECT statement. Multiple rows at a time.
question is.. does the trigger fire once for that insert into ... select or does it fire for each new row inserted by that statement. I was under the impression that it fires for each row but i'm not sure now because the table the trigger inserts to only has one row after the SP is run.
I created a trigger which looks like Create TRIGGER UpdateGenTables3 ON AWSWQ_Quizzes After insert as Begin SET NOCOUNT ON declare @TableName varchar(20),@Title varchar(20),@code varchar(20),@Gcode varchar(20),@uppercode varchar(20), @Description varchar(50) DECLARE DemoCursor CURSOR FOR Select QuizID,QuizTitle from AWSWQ_Quizzes group by QuizID,QuizTitle OPEN DemoCursor FETCH Next From DemoCursor INTO @code,@Title WHILE @@FETCH_STATUS = 0 BEGIN
INSERT INTO gen_tables (TABLE_NAME, CODE, SUBSTITUTE,UPPER_CODE,DESCRIPTION,OBSOLETE_DESCRIPTION) VALUES ('PRE_REQ_TESTS', @code, '', @code, @title, '') FETCH Next From DemoCursor INTO @code,@Title END CLOSE DemoCursor DEALLOCATE DemoCursor
end
The problem with my trigger is .I am not able to insert into my table AWSWQ_Quizzes the error i get when i try to insert is
Violation of PRIMARY KEY constraint 'pkGen_TablesTABLE_NAME'. Cannot insert duplicate key in object 'Gen_Tables'. The statement has been terminated. ---> System.Data.SqlClient.SqlException: Violation of PRIMARY KEY constraint 'pkGen_TablesTABLE_NAME'. Cannot insert duplicate key in object 'Gen_Tables'.
I have been trying to implement a trigger that is fired when a record is inserted in a table, provided that certain fields in the inserted record meet specific criteria (what you'd find in a where clause). All examples I have come across so far involve cases where the trigger is fired off everytime there is an insert regardless of what values are being inserted.
Basically, Assume a table "Address" with colums House Number, Street Name, City, State, Zip Code. How do I make my trigger fire ONLY when a record with City = 'Boston' is inserted??
If I have a trigger on a field in a table, and I update one record trigger fire properly. If I do a update to that same field on all records in the table the trigger does not fire. I the error in the trigger, or do I need to change my update statement?
I would like to update foreign keys using triggers. At least thats what I think is the solution when having multiple references to a table in order to avoid the "cycles or multiple cascade paths" error message.
Anyway, here are three tables Dentist table dentist_id int identity (auto increment)
Patient table: patient_id int identity (auto increment)
Appointment: apointment_id int identity (auto increment) id_dentist int FK to dentist_id id_patient int FK to patient_id
I am gooling but cant find a way to make a trigger run only when the dentist_id from dentist table is updated. Also, is there a way to get the new id and old id somehow? I saw some posts with new.dentist_id and old.dentist_id but apparently is not for sql server.
Hi i have a view that contain multiple tables from my database and i want to view it on datagridview and update it's data some people says you can update joined tables using instead of triggers how is that ?is there any example ?
How do I set up an insert trigger to copy all of the inserted data to another table? In other words, when someone adds a new paramater in the params table, I want to automatically create a matching set of data in the goals table. Thanks,Krista
Hi There are 2 databases db1 and db2 on SQL server 7 with tables 1 and 2 respectively. Both these tables have a field called Quantity. IF quantity gets updated in table 1 then that change should be immediately reflected in table2. I though an update on table1 would serve the purpose but this trigger doesn't seem to be working however there are no errors during the syntax check. The code for the trigger that i have used on table 1 is as below CREATE TRIGGER [Qty_UPDATE] ON [Table1] FOR UPDATE AS IF UPDATE (Qty) BEGIN UPDATE db2.dbo.table2 SET db2.dbo.table2.qty = qty END Please help Thanks in advance
I have a table which when certain columns are updated, need a trigger to fire to update a next schedule date in that same table for that record. I can write the trigger, but my question for performance and efficiency is which approach would be better. Separate triggers fo the 8 columns, or a large trigger with an If to check if these columns are updated. Thanks
