T-SQL (SS2K8) :: Insert / Update Triggers When Insert Run Via Script
Oct 23, 2014
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 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 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?
table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt
process: 1. get data from an existing view and insert in temptable 2. truncate/delete contents of table1 3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted) 4. insert data in table2 which are not yet present (comparing ID in t2 and temptable) 5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)
* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.
since ten years perhpas the society has a database with some table (person, country, and so on)since last years , the project manager decides to create a new database with some new design because before some information was save in one table well, the problem is when we pass all application to the new model we've notice that some id are not the same !
sample :
table Language old model ------------------------- id frenchDescription english description ---------------------------------------- 3 Anglais English ..... ......
table Language new model
id frenchDescription english description ----------------------------------------- 5 Anglais English ..... .....
Alright , you can understand that the new model must be the same id => 3
I currently have a AFTER UPDATE trigger that looks at a specific column being updated. It works great, but I am now needing to also capture the field data when it is initially created as well. Do I need to build a sperate FOR INSERT trigger to capture the initial column data when inserted, or is it possible to build it into my current AFTER UPDATE trigger?
Here is my current trigger: CREATE TRIGGER [dbo].[xResponsible_By] ON [dbo].[PARTS_REQUESTOR] AFTER UPDATE
I am currently in the process of writing an INSERT/UPDATE trigger in SQL 2012, the scenario is, I have a table called Quote, what I want to happen is when a record gets added to the table with a field called Approved, populated with a value or a record gets Updated and the Approved field gets populated I want the trigger to execute a stored procedure, passing through a value from a field called Account within the Quote table as a variable. I know I can achieve the action using one trigger "AFTER INSERT, UPDATE" but I am struggling on forming the rest of the trigger.
I have an address table, and a log table will only record changes in it. So we wrote a after udpate trigger for it. In our case the trigger only need to record historical changes into the log table. so it only needs to be an after update trigger.The trigger works fine until a day we found out there are same addresses exist in the log table for the same student. so below is what I modified the trigger to. I tested, it seems working OK. Also would like to know do I need to use if not exists statement, or just use in the where not exists like what I did in the following code:
ALTER TRIGGER [dbo].[trg_stuPropertyAddressChangeLog] ON [dbo].[stuPropertyAddress] FOR UPDATE AS DECLARE @rc AS INT ;
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?
I have a web form with a text field that needs to take in as much as the user decides to type and insert it into an nvarchar(max) field in the database behind. I've tried using the new .write() method in my update statement, but it cuts off the text after a while. Is there a way to insert/update in SQL 2005 this without resorting to Bulk Insert? It bloats the transaction log and turning the logging off requires a call to sp_dboptions (or a straight-up ALTER DATABASE), which I'd like to avoid if I can.
Hello,I am writing a stored procedure that will take data from severaldifferent tables and will combine the data into a single table for ourdata warehouse. It is mostly pretty straightforward stuff, but there isone issue that I am not sure how to handle.The resulting table has a column that is an ugly concatenation fromseveral columns in the source. I didn't design this and I can't huntdown and kill the person who did, so that option is out. Here is asimplified version of what I'm trying to do:CREATE TABLE Source (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,ssn CHAR(9) NOT NULL )GOALTER TABLE SourceADD CONSTRAINT PK_SourcePRIMARY KEY CLUSTERED (grp_id, mbr_id)GOCREATE TABLE Destination (grp_id INT NOT NULL,mbr_id DECIMAL(18, 0) NOT NULL,birth_date DATETIME NULL,gender_code CHAR(1) NOT NULL,member_ssn CHAR(9) NOT NULL,subscriber_ssn CHAR(9) NOT NULL )GOALTER TABLE DestinationADD CONSTRAINT PK_DestinationPRIMARY KEY CLUSTERED (grp_id, mbr_id)GOThe member_ssn is the ssn for the row being imported. Each member alsohas a subscriber (think of it as a parent-child kind of relationship)where the first 9 characters of the mbr_id (as a zero-padded string)match and the last two are "00". For example, given the followingmbr_id values:1234567890012345678901123456789021111111110022222222200They would have the following subscribers:mbr_id subscriber mbr_id12345678900 1234567890012345678901 1234567890012345678902 1234567890011111111100 1111111110022222222200 22222222200So, for the subscriber_ssn I need to find the subscriber using theabove rule and fill in that ssn.I have a couple of ideas on how I might do this, but I'm wondering ifanyone has tackled a similar situation and how you solved it.The current system does an insert with an additional column for thesubscriber mbr_id then it updates the table using that column to joinback to the source. I could also join the source to itself in the firstplace to fill it in without the extra update, but I'm not sure if theextra complexity of the insert statement would offset any gains fromputting it all into one statement. I plan to test that on Monday.Thanks for any ideas that you might have.-Tom.
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?
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??
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
I wrote a trigger that works fine when I insert record by record in the DB. However, when I run a Stored Proc to insert a bunch of records at the same time, the trigger only works for the last record.
Anyone has a clue or a possible solution. The trigger is well tested and works fine.
Hi everybody,I just wrote my first two triggers and from the minimal amount of testing Ihave done, they work! However, I was hoping I could get some feedback fromthose of you more experienced in writing triggers.Here is the first one:CREATE TRIGGER DecreInters ON InteractionFOR DELETEASdeclare @stu INTdeclare @num INTselect @stu = Student_FK from deletedselect @num = (select Inters from Student where Student_Key = @stu)UPDATE StudentSET Inters = @num - 1FROM StudentWHERE Student.Student_Key = @stuHere is the second one:CREATE TRIGGER IncreIntersON InteractionAFTER INSERTASdeclare @stu INTdeclare @num INTdeclare @last_rec INTselect @last_rec = @@IDENTITYselect @stu = (select Student_FK from Interaction where Interaction_ID =@last_rec)select @num = (select Inters from Student where Student_Key = @stu)UPDATE StudentSET Inters = @num + 1FROM StudentWHERE Student.Student_Key = @stuAre there any shortcuts I could use or things I could do to make thesetriggers more efficient. Please give me some feedback and let me know ofany problems that might possibly be caused due to my doing this improperly.Thanks ahead,Corey
Can I roll back certain query(insert/update) execution in one page if query (insert/update) in other page execution fails in asp.net.( I am using sqlserver 2000 as back end) scenario In a webpage1, I have insert query into master table and Page2 I have insert query to store data in sub table. I need to rollback the insert command execution for sub table ,if insert command to master table in web page1 is failed. (Query in webpage2 executes first, then only the query in webpage1) Can I use System. Transaction to solve this? Thanks in advance
I have database that holds column depicting various events and the last time that the event occurred.I query the database and I get a result like this:
Event Update Time Event A 2014-10-14 00:35:00.000 Event B 2014-10-14 01:30:00.000 Event C 2014-10-14 00:35:00.000
Event L 2100-01-01 00:00:00.000..I would like to create another database to hold the various timestamps for each event. Some of these events happen every 2 minutes, others hourly, daily or monthly.I can query the source every minute. I do not want duplicate entries in the destination when the 'Update Time' has not been updated. Is it better to query the destination for the latest 'Update Time' value and only INSERT the new record when the time has been updated or just put a UNIQUE constraint on the Event and UpdateTime columns?
WITH cte_OrderProjectType AS ( select Orderid, min(TypeID) , min(CTType) , MIN(Area) from tableA A inner join tableB B ON A.PID = B.PID left join tableC C ON C.TypeID = B.TypeID LEFT JOIN tableD D ON D.AreaID = B.ID group by A.orderid )
This query uses min to eliminate duplicates. It takes 1.30 seconds to complete..
Is there any way I can improve the query performance ?
1) TableA : Which contains 5 columns(Column1,..........Column5) 2)TableB : Which contains 10 columns(Column1,..........Column10)
TableB contains millions of data.Now I want select all 5 columns from tableA but combination of Column1,Column2,Column3 if present in tableB, then i want exclude that records.I am doing as below:
select * from TableA a join TableB b a.column1!=b.column1 and a.column2!=b.column2 and a.column3!=b.column3 )
But query is taking almost 5 minutes. Is there is another approach?
hello friends my one insert code lines is below :) what does int32 mean ? AND WHAT IS DIFFERENT BETWEEN ONE CODE LINES AND SECOND CODE LINES :)Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString) Dim cmd As New SqlCommand("Insert into table1 (UserId) VALUES (@UserId)", conn) 'you should use sproc instead cmd.Parameters.AddWithValue("@UserId", textbox1.text) 'your value Try conn.Open()Dim rows As Int32 = cmd.ExecuteNonQuery() conn.Close()Trace.Write(String.Format("You have {0} rows inserted successfully!", rows.ToString())) Catch sex As SqlExceptionThrow sex Finally If conn.State <> Data.ConnectionState.Closed Then conn.Close() End If End Try MY SECOND INSERT CODE LINES IS BELOWDim SglDataSource2, yeni As New SqlDataSource() SglDataSource2.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString SglDataSource2.InsertCommandType = SqlDataSourceCommandType.Text SglDataSource2.InsertCommand = "INSERT INTO urunlistesi2 (kategori1) VALUES (@kategori1)" SglDataSource2.InsertParameters.Add("kategori1", kategoril1.Text)Dim rowsaffected As Integer = 0 Try rowsaffected = SglDataSource2.Insert()Catch ex As Exception Server.Transfer("yardim.aspx") Finally SglDataSource2 = Nothing End Try If rowsaffected <> 1 ThenServer.Transfer("yardim.aspx") ElseServer.Transfer("urunsat.aspx") End If
If I have a table with 1 or more Nullable fields and I want to make sure that when an INSERT or UPDATE occurs and one or more of these fields are left to NULL either explicitly or implicitly is there I can set these to non-null values without interfering with the INSERT or UPDATE in as far as the other fields in the table?
EXAMPLE:
CREATE TABLE dbo.MYTABLE( ID NUMERIC(18,0) IDENTITY(1,1) NOT NULL, FirstName VARCHAR(50) NULL, LastName VARCHAR(50) NULL,
[Code] ....
If an INSERT looks like any of the following what can I do to change the NULL being assigned to DateAdded to a real date, preferable the value of GetDate() at the time of the insert? I've heard of INSTEAD of Triggers but I'm not trying tto over rise the entire INSERT or update just the on (maybe 2) fields that are being left as null or explicitly set to null. The same would apply for any UPDATE where DateModified is not specified or explicitly set to NULL. I would want to change it so that DateModified is not null on any UPDATE.
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) VALUES('John','Smith',NULL)
INSERT INTO dbo.MYTABLE( FirstName, LastName) VALUES('John','Smith')
INSERT INTO dbo.MYTABLE( FirstName, LastName, DateAdded) SELECT FirstName, LastName, NULL FROM MYOTHERTABLE
I have a web page where the user can select the language (FR, EN, BG, ...) in a drop down list.
Next to the drop down list there is a text box where user can type the some text (translation).
User can add several description
On my web page, i have a button this button collect all information create an xml file and save all in database (sql server 2008)
that's work fine for some language => FR, EN and so on
But for bulgarian (bulgare) and greece there are some problem...
Some characters when i display it in sql look like => ???s??. ? d??ta?? a?t?
The value encoded by the user is => Ένωσης. Η διάταξη αυτή
but the result after t sql xpath is => ???s??. ? d??ta?? a?t?
Here is it my sql code where you can find my temporary table and my xml file and my xpath query
declare @tblTranslation table (idDocID int, languageID varchar(10), value varchar(500)) declare @Translations XML
set @Translations = '<?xml version="1.0" ?><Items><Item><eleKey>EN</eleKey><eleValue>This is a test</eleValue></Item><Item><eleKey>FR</eleKey><eleValue>test</eleValue></Item><Item><eleKey>BG</eleKey><eleValue>Ένωσης. Η διάταξη αυτή</eleValue></Item><Item><eleKey>HR</eleKey><eleValue></eleValue></Item><Item><eleKey>RO</eleKey><eleValue></eleValue></Item></Items>'
-- 2) fill the temporary table with information from the xml file
INSERT INTO @tblTranslation(idDocID, languageID, value) SELECT 1 , Convert(nvarchar(max), i.query('eleKey/text()')) as colKey , Convert(nvarchar(max), i.query('eleValue/text()')) as colValue -- FROM @translations.nodes('/Items/Item') as x(i) SELECT * FROM @tblTranslation
