Hi,
I'm taking an Excel spreadsheet (that could have around 30k rows) and processing it in SSIS. I essentially have a flag in one of the spreadsheet cols that indicates whether the record is already in the database or not.
I'm splitting the data using a conditional split on this column and using a OLE DB Destination (Fast Load) to perform the inserts and a OLE DB Command to fire a stored procedure to perform any updates. Both the OLE DB Destination and the stored procedure are hitting the same table and the two operations could be executing at the same time as they both appear directly after the Conditional Split, so the OLE DB Destination is set NOT to lock the table.
This seemed to work OK until recently. I've just added 2 triggers onto the table in question which I don't want to fire 30,000 times during the import. As the OLE DB Destination is set to use Fast Load, it doesn't fire the triggers - cool. In the update stored procedure it disables the trigger before performing it's update and re-enables the trigger when finished. Currently this does mean that if you only had updates, the trigger could be enables/disabled 30,000 times. That sounds kinda bad, but I don't really know if this carries a large overhead or not?
If, when importing now you have both updates and inserts the whole process locks up. From looking at activity monitor, it seems as though the INSERT gets suspended.
Do I have a fundamental problem with how I've structured the Data Flow or am I just being really stupid in Enabling/Disabling a trigger that many times, which is probably causing the problem?
I am in the process of writing a stored procedure that takes data from a table, within a cursor, and puts that data to the network server via the xp_cmdshell procedure. The xp_cmdshell command is at the end of the cursor in the SP.
I have worked with the admins on getting the proper permissions to execute this procedure and have tested it by running the procedure directly in Query Analyzer and by calling it in Query Analyzer successfully.
However when I try to call this procedure from another programming envirnoment, once the xp_cmdshell process is called, the procedure locks up and blocks the rest of the process, including the other software programming environment. When I look at the error log, it indicates that correct command, as a matter of fact I have copied the log file command into Query Analyzer and ran the procedure successfully. So for some reason, the statement is not being committed or there is some kind of threading issue I do not know how to address.
If anyone out there has any suggestions or has a simular problem in the past and knows how to address it, it would be greatly appreciated.
I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?
Hi, Is it possible to group all my update/insert (one table) ? Cause I hope to have all the update/insert in one Trigger execution. I need to sum up some figures here. I am using ASP.NET 2.0, SQLOLEDB and SQL 2005. Please Help and Thank you.
I would like to update a record in a table when a record is added. The dilema is I want to update the record I am adding. I tried an insert trigger ( it doesn't fail, it just doesn't update) If I use the same syntax in TSQL after the record has been inserted it handles the update properly. SO I think the syntax is OK. I have the trigger below. The question is - Is there a way to do this? Thanks
CREATE TRIGGER transaction_ins_tr ON dbo.transactions FOR INSERT AS DECLARE @pat_id varchar(5), @location_name varchar(50), @account_id char(12), @tran_num int --get patient id SELECT @pat_id = patient_id FROM inserted select @account_id = account_id from dbo.patient pa where pa.patient_id = @pat_id --get location select @location_name = df_sitename from development..patient_ dp where dp.account_id = @account_id -- update trans location update dbo.transactions set df_sitename = @location_name where tran_num = @tran_num
Tbl1 inserts 1 record(with some fields populated) in tbl2. then I need get values from tbl3 to populate the rest of the fields in tbl2(update the record). tbl1 = tblallBag_data tbl2 = tblBag_data tbl3 = tblShipping_sched
I created a trigger in tbl1 to insert a record into tbl2 and it works fine.
CREATE TRIGGER trgtblBag_Data ON dbo.tbltblallBag_data FOR INSERT AS
INSERT INTO tblBag_data (work_ord_num, work_ord_line_num, bag_num, bag_scanned_by, bag_date_scanned, bag_quantity) SELECT work_ord_num, work_ord_line_num, bag_num, bag_scanned_by, bag_date_scanned, bag_quantity FROM inserted
How can I update tbl2? Should I create another trigger to update tbl2? Should I join the two tbls(tbl2 & tbl3) to find @work_ord_num = work_ord_num , @work_ord_line_num = work_ord_line_num
I have several tables that i need to add a trigger to. One such table is "Clusters". This table contains a column called ClusterFKey which should be the same value as the AutoID column in the Serverdata table (same db) and should be populated every time a record is added to the Clusters table. The value should be joined on the servername column's value in both the Serverdata and Clusters table.
So, when a record is added to Clusters, the ClusterFkey should equal the AutoID key in Serverdata where Clusters.servername = Serverdata.server and Clusters.ClusterFkey is null.
Hi!I wonder how to use conditions in the inserted table(in ainsert/update) trigger? The inserted table contain all the rows thathave been updated or inserted (for an update/insert trigger), but outof all these rows in inserted table, I only want the rows where aparticular field have been updated, for example if idkey have beenupdated it would be in inserted BUT I only want this row if the fieldamount have been updated. Can a use the UPDATE(column) some how? Anyideas?/Jenny
I would like to insert an update trigger which should achieve the following functionallity (as per trigger algorithm) but somehow it is not working. can you please help me since I am still green with SQL and programming, but I need to solve this before I can continue!
CREATE TRIGGER ActiveCard
ON AccessControl
FOR Insert (when i insert a new row in Access Control)
AS UPDATE UniqueCard
( to update the matching (match IDNum) row within UniqueCard table)
Set UniqueCard.Active = Inserted.Active (card and active are of the same type)
I have a table called drugs with a cost, date, and drugname field. I have a history table that I want to insert into anytime the fields are changed in the drugs table. I want to capture the old data along with the new cost. is that possible in a update trigger? What I have below doesn't like when it comes to setting the new cost
create trigger Updt_drugs on drugs for update as -- updates record with sql user and timestamp --created 1-12-02 tim cronin declare @newcost money begin set @newcost = select cost from inserted insert into drugsold ([oldcost],[cost],[cdate],[drug]) select [cost],@newcost,[cdate],[drug] from deleted dt end
I HAVE TWO TABLES IN THE DATABSE AND THE SECOND TALE SI FOR AUDITING. I WANT CREATE THE TRIGGER ON FIRST TABLE SO THAT I CAN PUT THE STATUS LIKE INSERT,UPDATE OR DELETE IN THE STATUS COLUMN IN SECOND TABLE. CAN SOMEBODY HELP IN WRITING THAT TRIGGER..? HOW CAN I DETERMINE WAETHER THE RECORD IS BEEN INSERTED OR UPDATED OR DELETED.
DO I HAVE TO WRITE A SEPERATE TRIGGER FOR EACH ACTIVITY..OR I CAN WRITE IT IN THE SINGLE TRIGGER..?
I have a parent table with 27 Columns and Child Table with 37 colums - when even there is an update in any of the columns on Parent or Child table, I require new record inserted into Audit_Parent and Audit_child table. Please help with SQL Code on Create Trigger and insert records into Audit_parent and Audit_child when an Update occurs on any of the columns. Insert into AuditParent and AuditChild should occur whenever there is an update on either Parent or child table.
I am new to triggers and need help on the following:
I have a hourly table that inserts new rows every hour but I need to either Insert or Update the daily table with the sum of the reading from the hourly table. If a row exist in the daily table with the date of the hourly table, then I need to update this row but if it doesn't exist, I need to insert this row.
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 have to create a trigger that will log who changed information on a table and when (NOT what they have changed).
My idea is to get the users name and see if it is in a table if not create it and get the associated ID, also get the ID of table that was accessed along with the ID of the type of task that was performed. Take this data and insert it into a table.
I currently have 2 tables as follows:CREATE TABLE [CRPDTA].[F55MRKT119](mhan8 int,mhac02 varchar(5),mhmot varchar(5),mhupmj int)GOCREATE TABLE [CRPDTA].[F55MRKT11](mdan8 int,mdac02 varchar(5),mdmot varchar(5),mdmail int,mdmag int,mdupmj int)What I would like to do is place a trigger on F55MRKT119 which willinsert records to the F55MRKT11 if they do not exist in that tablebased on the [mdan8] field. If the record does exist I would likeUpdate the corresponding record and increment either the [MDMAIL] orthe [MDMAG] based on the inserted [MHMOT]. What I have so far is asfollows:TRIGGER #1:CREATE TRIGGER trgIns_Summary ON [CRPDTA].[F55MRKT119]FOR INSERTASBEGININSERT INTO CRPDTA.F55MRKT11select INS.MHAN8, INS.MHAC02, INS.MHMOT,case when INS.MHMOT='MAG' then 0 ELSE 1 end,case when INS.MHMOT='MAG' then 1 ELSE 0 end,'0' from INSERTED INSWHERE ins.mhan8 not in(select mdan8 from crpdta.f55MRKT11)ENDTRIGGER #2:CREATE TRIGGER trgUpd_Summary ON [CRPDTA].[F55MRKT119]FOR UpdateASBEGINUPDATE CRPDTA.F55MRKT11SET MDMAIL= case when INS.MHMOT='MAG' then 0+MDMAILwhen INS.MHMOT<>'MAG' then 1+MDMAIL end,MDMAG= case when INS.MHMOT='MAG' then 1+MDMAGwhen INS.MHMOT<>'MAG' then 0+MDMAG endfrom INSERTED INS JOIN CRPDTA.F55MRKT11on(ins.mhan8=mdan8)ENDFor instance if I do the following insert:INSERT INTO CRPDTA.F55MRKT119VALUES('212131','VK4','AL4','0')thenINSERT INTO CRPDTA.F55MRKT119VALUES('212131','VK4','MAG','0')This is what I expect in both tables:[CRPDTA.F55MRKT119] (2 Records)MHAN8 MHAC02 MHMOT MHUPMJ------ ------ ----- ------212131 VK4 AL4 0212131 VK4 MAG 0[CRPDTA.F55MRKT11] (1 Record)MDAN8 MDAC02 MDMOT MDMAIL MDMAG MDUPMJ----- ------ ----- ------ ----- ------212131 VK4 AL4 1 1 0The insert part works fine in that it iserts in both tables with thecorrect values. However it seems as if the Update protion is failingfor some reason. WHat I have tried so far is setting the trigger orderfor the update to run first and vice-versa, but still no luck. Anyhelp would be appreciated.
Hi,Does anyone know of a simple way to do this? I want to create aninsert trigger for a table and if the record already exists based onsome criteria, I want to update the table with the values that arepassed in via the insert trigger without having to use all the 'set'statements for each field (so if we add fields in the future I won'thave to update the trigger). In other words, I want the trigger codeto look something like this:if exists (select * from TableA where Fld1 = inserted.Fld1) then//don't do insert, do an update instead (would i want to rollback here?and will I have access to the 'inserted' table still?)Update TableASet TableA.<all the fields> = Inserted.<all the fields>where Fld1 = inserted.Fld1end ifAny help or ideas would be appreciated.Thanks,Teresa
How would I write a trigger that updates the values of a Descriptioncolumn to upper case for even IDs and to lower case for odd IDs? Ineed this trigger to fire for INSERT and UPDATE events.
In recent testing, I have found that the trigger function UPDATE under SQL Server 6.5 SP5 behaves differently then SP4. For insert, the update function returns true on items that have not been explicitly inserted.
Is this a bug? Is this a new feature change? Where can I get more information on a bug list for SP5?
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 created a job that fill and update a database from a source db with same structure.
INSERT code is made up comparing the pk column in the source and dest db, the missed ones are filled in the destination.
UPDATE: check col by col, if any change value exists, updated is performed with the following statement for any tables in the DB
UPDATE tableADest
SET col1=source.Col1, col2=source.col2, ... coln=source.coln
FROM tableASource source
INNER JOIN tableAdest dest
ON dest.colpk = source.colpk
The main problem is that I cannot identify the row update in the souce db, everytime I have to compare the whole equivalent tables (source and dest db), because there are not timestamp, updated cols or any cols usefuls, to have a subset of data and find any new or upadeted rows.
I tried replication, but it does not work on that db.
So I created a trigger for each table where new insert or updated row must be detected. The PKs are saved on tables.
The problem is that when I run the application it hold-on, when triggers are disabled the application run fine.
How can I use trigger on several tables without affectrunning application?
I have 2 tables (Dept and Emp) The columns in table Dept are Deptno and Deptname. Deptno is bigint and it is primary key. In Emp table, columns are Empno(PK) ,EmpName and Deptno(foreign key referring to Dept)
To Insert or Update record in Emp through application, value of Deptno is coming as 0(Zero). I want the value of Deptno to be inserted or updated as null if the value is Zero (0). How to do this in sql server 2005 by using trigger on table Emp
I am new to triggers and surely could use some help.
I can create a trigger to insert related records based on the main tables ID and insert that value into other related tables fine... but...
How do I create a trigger that can insert a record into one table for a columns given value and then insert a record into another table for another given value?
For instance: New row...
Table1, Column1 (PK) has a value of 101 Table1, Column2 has a value of 'Blue'.
// When a new row is created in Table1 and Column2 has a value of 'Blue'... I want to insert a new row into Table2 - with Table1 Column1's value.
// Now if Table1, Column2 has a value of 'Red' when the new row was created... I want to insert a new row into Table3 - with Table1 Column1's value. Not Table2
This has to be inserted into one or the other tables based on column2's value, not both. Then I want to populate the other related tables (Table4, Table5) with the regular insert statements based on Table1 Column1's value.
This (the conditional part above) has to work with an update to Table1 also. So if someone came back to that record and changed Column2's value from 'Blue' to 'Red', it would have to delete the appropriate record in Table2 and then insert the new row into Table3 and visa-versa.
I'm trying to update (increment) Company.SumtotalLogons from CompanyUsers.NumberOfLogons where CompanyUsers.CompanyID = Company.CompanyID
I'd like to either write a formula (if it is even possible to fire a formula from one table update/insert to increment a field in another table), or a stored procedure that triggers an auto update/append into Company.SumTotalLogons
I know this is possible in access, so i'm wondering how to go about it in ms-sql?
I have never used triggers before and I have tried to solve one problem. If I have the column "currency" in a table and want to make sure that the entered value i valid in relation to another table that contains valid currency formats, I did it like this:
--------------------------------- CREATE TRIGGER [trigger_checkCurrency] ON [dbo].[Client] FOR INSERT, UPDATE AS declare @currency as char(50) declare @country as char(50)
declare cur cursor for SELECT currency, country FROMinserted
OPEN cur fetch cur into @currency, @country WHILE @@FETCH_STATUS = 0 BEGIN if not exists(select * from listinfoid where listname = 'currency' and listid = @currency) begin set @currency = (cast(@currency as varchar (3)) + ' is not a valid currency') CLOSE cur DEALLOCATE cur RAISERROR (@currency,16,-1) with log return end if not exists(select * from listinfoid where listname = 'country' and listid = @country) begin set @country = (cast(@country as varchar (3)) + ' is not a valid contry') CLOSE cur DEALLOCATE cur RAISERROR (@country,16,-1) with log return end else fetch cur into @currency, @country
END CLOSE cur DEALLOCATE cur update Client set currency = UPPER(currency), country = UPPER(country) ---------------------------------
I use a cursor to handle multiple rows in an update query. (SQL2000-server)
Is there an easier och better way to do this? I´m a bit unsure of this code.
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 all,SQL Server 2000 documentationhttp://www.microsoft.com/technet/pr...rt10/c3761.mspxstates that if view is using "NOT NULL" columns of a base table, theninsert/update performed on a view must provide dummy values for thosecolumns, and code of the trigger should ignore them.But I cannot reproduce this restriction. Code below pasted to QueryAnalysershows that I can not supply dummy values for "NOT NULL" fields when I updateview and still have update done. What do I miss ?VT/*--setup step 1. execute only inside of this commentSET NOCOUNT ONCREATE TABLE TestTable(keyField INT IDENTITY(1,1),dataField1 INT NOT NULL,dataField2 INT DEFAULT 1 NOT NULL)*//* --setup step 2. execute only inside of this commentCREATE VIEW TestView ASSELECT * FROM TestTable*//*--setup step 3. execute only inside of this commentCREATE TRIGGER TestViewTrig_IUON dbo.TestViewINSTEAD OF UPDATEASBEGINSET NOCOUNT ONUPDATE TestTableSETDataField1 = inserted.DataField1,DataField2 = inserted.DataField2FROMTestTable ttINNER JOINinsertedON inserted.KeyField = tt.KeyFieldEND*//*--setup step 4. execute only inside of this commentINSERT INTO TestTable (DataField1,DataField2) Values (1,2)INSERT INTO TestTable (DataField1,DataField2) Values (3,4)INSERT INTO TestTable (DataField1,DataField2) Values (5,6)*/SELECT * FROM TestView-- SQL Server lets me not specify DataField2 when update DataField1 orreverse,-- which is opposed to what documentation saysUPDATE TestView SET DataField1 = DataField1 + 1 where KeyField = 2UPDATE TestView SET DataField2 = DataField2 + 1 where KeyField = 3SELECT * FROM TestView/*-- remove test environmentDROP VIEW TestViewDROP TABLE TestTable*/
I want to create a trigger that, when a field is updated or a record is inserted, counts the number of words in "field1", inserting this numeric value into another field in the same table. What would be the most efficient, fastest method for doing this?
So, I've written an Instead of Trigger to update a foreign key field based on information in another field of the same record.
To add some error handling to the process I updated the Trigger to insert any records that don't have legitimate foreign keys into a second table.
This process works great when I test it by just adding a record using the table view in the SQL Management Studio or through a query run in the query browser.
However, when a record is added via an ODBC connection I get foreign key constraint errors and records are not added to the second table. If the foreign key is legit the record is added and the part of trigger that updates that keyed field executes just fine.
Is anyone aware of this issue? Is there a way around it?
I found the following MSKB article but I'm not sure if it applies to my situation: http://support.microsoft.com/kb/304096
Here's my current code, if that track the problem in anyway:
Code:
ALTER TRIGGER UpdateTicketID ON Email Instead of INSERT AS IF ((Select charindex('{', [subject]) FROM Inserted) = 0) BEGIN INSERT INTO BadEmail ([Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLInk, SendTo, Cc, ContactID) Select [Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLink, SendTo, Cc, ContactID From Inserted END ELSE IF ((Select substring([subject], charindex('{', [subject])+1, (charindex('}', [subject]) - charindex('{', [subject]))-1) From Inserted) NOT In (Select TicketID From Ticket)) BEGIN INSERT INTO BadEmail ([Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLInk, SendTo, Cc, ContactID) Select [Subject], Sender, Body, EntryID, LastModificationTime, AttachmentLink, SendTo, Cc, ContactID From Inserted END ELSE BEGIN INSERT INTO Email ([Subject], Sender, Body, ticketID, EntryID, LastModificationTime, AttachmentLink, SendTo, Cc, ContactID) Select [Subject] , Sender , Body , substring([subject], charindex('{', [subject])+1, (charindex('}', [subject]) - charindex('{', [subject]))-1) , EntryID , LastModificationTime , AttachmentLink , SendTo , Cc , ContactID From Inserted END
how to do this i have table of employee ,evry employee have a unique ID "empid" empid VAL_OK -------------------------- 111 0 222 0 333 0
now insert multiple insert to my work_table shifts for all month for evry employee like this (this is work_table) empid date val -------------------------------------------------- 111 01/02/2008 1 111 02/02/2008 2 ............... 111 29/02/2008 5 --next employee 222 01/02/2008 1 222 02/02/2008 4 ............... 222 29/02/2008 6 --next employee 333 --next employee 444 --next employee 555 -------------------------------------------------------------
now i need for evry OK insert (for all month) each employee go to the TB_Employee and update each employee once !! from VAL_OK=0 to VAL_OK=1 like this
empid VAL_OK -------------------------- 111 1 222 1 333 1 ---------------------- like this i know who is the employee have shift for all month and who NOT !
i think it like this
Code Snippet Create trigger for_insert on tb_work For insert begin if @@rowcount = 1 Update tb_employee Set val_ok= 1
else /* when @@rowcount is greater than 1, use a group by clause */ Update tb_employee set val_ok= 1 select empid from tb_work group by tb_work.empid
