1) I am running a DTS job to fetch some rows from Oracle
2) The job populates the Table A as step 1
3) Then it fires a update statement which updates the rows in Table B.
Here's the statement
UPDATE Table B SET
time = case when (select median from Table A where sno = sno and Table
A.stno=70 ) is null then time
else (select median from Table A where Table B.sno = Table A.sno and
Table A.sstno=70) end ,
end
WHERE EXISTS (select sstno from Table A where Table B.sno = Table
A.sno)
There is a trigger on table B which should fire as soon as the
value>15.
When I fire the update statement direcly with a higher value than it
fires the trigger.
update table B set time=17 where b.sno=1000
But not when the job runs...
I am puzzled.
Thoughts?
I have a table where table row gets updated multiple times(each column will be filled) based on telephone call in data.  Initially, I have implemented after insert trigger on ROW level thinking that the whole row is inserted into table will all column values at a time. But the issue is all columns are values are not filled at once, but observed that while telephone call in data, there are multiple updates to the row (i.e multiple updates in the sense - column data in row is updated step by step),
I thought to implement after update trigger , but when it comes to the performance will be decreased for each and every hit while row update.
I need to implement after update trigger that should be fired on column level instead of Row level to improve the performance?
I have an update trigger I created that updates a field based on the user who last updated the record. Under 7 the only way it would work was to have recursive triggers firing turned on. Under 2000 might there be a btter solution. The code is below. Thanks
CREATE trigger tr_cmsUpdt_MARS on dbo.PATIENT_MEDICATION_DISPERSAL_ for UPDATE as -- updates record with sql user and timestamp --created 11-28-00 tim cronin DECLARE @muser varchar(35), @rec_lock_status int, @ptacpt_status int set @muser = current_user begin UPDATE PATIENT_MEDICATION_DISPERSAL_ set MODIFIED_BY = @muser, MODIFIED_TS = getdate() from deleted dt WHERE --DT.MODIFIED_BY <> 'DBO' AND PATIENT_MEDICATION_DISPERSAL_.RECORD_ID = dt.RECORD_ID end
Hi. This SHOULD be something simple, but I am apparently missing something. I have a Users table where a user's status is a varchar(100). I'm trying to implement a trigger so that when a user's status is changed to any string other than what it was before the update the trigger would change the LastUpdated field to current date/time.
Here's the trigger, I replaced the update of LastUpdated with a simple print statement. For some reason, it seems like the trigger is firing after the update statement has committed because the values of @m_status_new and @m_status_old are always the same so this trigger always prints 'status has not changed.' What am I doing wrong? Thank you greatly for any help provided.
USE myDB IF EXISTS (SELECT name FROM sysobjects WHERE name = 'tr_update_users' AND type = 'TR') DROP TRIGGER tr_update_users GO
CREATE TRIGGER tr_update_users on dbo.Users FOR UPDATE AS
DECLARE @m_status_new varchar(100), @m_status_old varchar(100), @m_UserID int
I have a trigger on a table that just updates a last_modified_date and this works fine on our production server. Now I have to update some data and I do not want the trigger to fire. I cannot disable or drop the trigger because the productions systems needs the trigger. Anyone an idea of how to solve this problem?
Anyone got an idea why this does not work. I have a trigger that is supposed to fire as an INSERT is done on a table. If I manually insert (insert into....), the trigger fires. If I use BCP to insert, the trigger DOES NOT FIRE.....
OK, I'm at a loss..it must be staring me right in the face.
I have a junction table that relates 2 tables, with a unique key of the composit of the 2 keys. There is also an indicator that says 1 relationship between the 2 tables is "primary" and there should only be one of those. So I figured a trigger to take care of it...but I can't seem to get it working...I wrote sample sql to mimic the inserted table as well, and it seems correct, but the trigger just does not fire.
Any ideas?
CREATE TABLE [dbo].[PIF_MEP99] ( [PIFRecID] [int] NOT NULL , [MEPRecID] [int] NOT NULL , [PrimaryInd] [char] (1) NOT NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[PIF_MEP99] WITH NOCHECK ADD CONSTRAINT [PIF_MEP99_PK] PRIMARY KEY CLUSTERED ( [PIFRecID], [MEPRecID] ) ON [PRIMARY] GO
INSERT INTO PIF_MEP99(PIFRecID, MEPRecID, PrimaryInd) SELECT 1,1,'Y' UNION ALL SELECT 2,1,'N' UNION ALL SELECT 3,2,'N' GO
CREATE TRIGGER dbo_PIF_MEP99_tr_Rule1 ON dbo.PIF_MEP99 FOR UPDATE, DELETE AS SET NOCOUNT ON
-- Rule 1: Prevent and MEP from having more than 1 PIF as Primary
IF Exists ( SELECT * FROM inserted i INNER JOIN PIF_MEP99 p ON i.MEPRecID = p.MEPRecID AND i.PrimaryInd = 'Y' AND p.PrimaryInd = 'Y') BEGIN ROLLBACK TRAN RAISERROR 500003 'Attempting to Insert 2 Primary PIFs for an MEP' END GO
SELECT * FROM PIF_MEP99 GO
SELECT * FROM (SELECT 4 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i INNER JOIN PIF_MEP p ON i.MEPRecID = p.MEPRecID AND i.PrimaryInd = 'Y' AND p.PrimaryInd = 'Y' GO
BEGIN TRAN INSERT INTO PIF_MEP99 (PIFRecID, MEPRecID, PrimaryInd) SELECT 4,1,'Y' COMMIT TRAN GO
SELECT * FROM PIF_MEP99 GO
SELECT * FROM (SELECT 5 AS PIFRecID,1 AS MEPRecID,'Y' AS PrimaryInd) AS i INNER JOIN PIF_MEP p ON i.MEPRecID = p.MEPRecID AND i.PrimaryInd = 'Y' AND p.PrimaryInd = 'Y' GO
DROP TABLE PIF_MEP99 GO
Brett
8-)
Hint: Want your questions answered fast? Follow the direction in this link http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
I have build a SQL Trigger that fires on the update of a specific column; this works perfectly when I test using SQL or even the SQL Server GUI but when I do the update from .NET it doesn€™t fire.
I have a dataset that gets modified and then I call sqlDataAdapter.Update(Dataset) - the row is successfully modified in the database but alas - the trigger isn't fired.
I have a table that is getting refreshed from DB2 using DTS (I believe the DBA is doing a DELETE and an APPEND). I have a trigger on this table ON APPEND, INSERT, but the trigger never fires. When I manually update the data, the trigger fires no problem...
Is DTS capable of updating a SQL Server table without firing the trigger?
I'm an Oracle guy, and this is my 1st experiences with SQL Server, so I'll put the code here and if you want to point out any bad practices (such as the way i converted the DB2 TIMESTAMP to a SQL Server DATETIME , please do.
FYI, the DB2 TIMESTAMP is getting loaded into the SQL Server table as a VARCHAR(26) Carl
CREATE TRIGGER trig_SAWakeUp ON tsnro FOR INSERT, UPDATE AS DECLARE @snro_stus char(10) DECLARE @snp_sht_dtm as datetime SELECT @snro_stus = snro_stus FROM tsnro IF (RTRIM(@snro_stus) = 'ASSIGNED') OR (RTRIM(@snro_stus) = 'REFRESHED') BEGIN SELECT @snp_sht_dtm = CONVERT(DATETIME, SUBSTRING(evt_dtm,1,19)) FROM tsnro
INSERT INTO TSNP_SHT_DTM (SNRO_STUS, SNP_SHT_DTM ) VALUES (@snro_stus, @snp_sht_dtm) END ELSE BEGIN INSERT INTO TSNP_SHT_DTM ( SNRO_STUS, SNP_SHT_DTM ) VALUES (@snro_stus, @snp_sht_dtm) END
i have an issue with an insert trigger sometimes not firing.
here is the trigger
CREATE TRIGGER Insert_tPABillToAddr ON [dbo].[tPA00175] FOR INSERT AS
INSERT into tPABillToAddr ( chrJobNumber )
SELECT chrJobNumber FROM inserted
when the user enters a new this table is to insert one column into another table. the thing is, sometimes it does not do the insert. any ideas as to why? it is a very uncommon thing, lets say once out of every 20 inserts does it fail. but it is crucial that it never fails.
1) I have two tables , chat and country tables , chat table has 3 columns(chat_id,language,chat_info) and media table has 2 fields(chat_id ,country), chat table will store all countries data (India,china,taiwan)
2) chat_id,language will be populated by insert statement, and chat_info by update statement, i have a below after update trigger on chat table,which should verify country from media table and copy respective record from chat table to chat_country table (chat_in,chat_cn,chat_tw - these tables will have same structure as chat table)
3) this trigger is not firing for some of the records ,no clue or info for which it is missing some records.
create TRIGGER [dbo].[chat_trigger] ON [test].[dbo].[chat] after update as BEGIN
Thanks for looking at this post. I currently have a trigger that fires when a row is inserted or deleted on a table. The idea behind the trigger is that when a row is inserted (representing a sub-category for images), the categories parent needs to have some work done on it. I currently have the trigger working just fine with single inserts and single deletes:
sql Code:
Original - sql Code
CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_sync ON smvcModImageManagerCategory FOR INSERT, DELETE AS DECLARE @pdfId INTEGER; DECLARE @parentPdfId INTEGER; DECLARE @grandparentPdfId INTEGER; DECLARE @parentId INTEGER; DECLARE @grandparentId INTEGER; DECLARE @tableName VARCHAR( 255 );
-- If I am being inserted or deleted, and I am not a top level -- category, then my parent's pdf record needs to be set so that -- the pdf file is updated IF (SELECT id FROM Inserted) IS NOT NULL BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Inserted), @parentId = (SELECT parentCategory FROM Inserted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId); END ELSE BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Deleted), @parentId = (SELECT parentCategory FROM Deleted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId) END
-- If I am not a top level category, set my parent's pdf to be -- updated IF @parentId <> -1 BEGIN SELECT @parentPdfId = (SELECT pdfManagerId FROM smvcModImageManagerCategory WHERE id = @parentId); UPDATE smvcModPdfManager SET data_last_updated = GETDATE() WHERE id = @parentPdfId; END GO
CREATE TRIGGER smvcModImageManagerCategory_insert_delete_pdfManger_syncON smvcModImageManagerCategoryFOR INSERT, DELETEAS DECLARE @pdfId INTEGER; DECLARE @parentPdfId INTEGER; DECLARE @grandparentPdfId INTEGER; DECLARE @parentId INTEGER; DECLARE @grandparentId INTEGER; DECLARE @tableName VARCHAR( 255 ); -- If I am being inserted or deleted, and I am not a top level -- category, then my parent's pdf record needs to be set so that -- the pdf file is updated IF (SELECT id FROM Inserted) IS NOT NULL BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Inserted), @parentId = (SELECT parentCategory FROM Inserted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId); END ELSE BEGIN SELECT @pdfId = (SELECT pdfManagerId FROM Deleted), @parentId = (SELECT parentCategory FROM Deleted), @grandparentId = (SELECT parentCategory FROM smvcModImageManagerCategory WHERE id = @parentId) END -- If I am not a top level category, set my parent's pdf to be -- updated IF @parentId <> -1 BEGIN SELECT @parentPdfId = (SELECT pdfManagerId FROM smvcModImageManagerCategory WHERE id = @parentId); UPDATE smvcModPdfManager SET data_last_updated = GETDATE() WHERE id = @parentPdfId; ENDGO
However, when I execute a statement like:
sql Code:
Original - sql Code
DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91'))
DELETE FROM smvcModImageManagerCategory WHERE (smvcModImageManagerCategory.id IN ('86','87','88','90','91'))
I get an error because the virtual 'Deleted' table has more than one record in it. So, what I really need is advice on how to turn the above trigger into something that will be able to handle multiple deletes.
Nothing fancy; just a trigger on a sharepoint table that supposed towrite a record to another SQL table.set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER TRIGGER [TV_UpdateFileSyncProgress]ON [dbo].[Docs]AFTER INSERTASBEGINSET NOCOUNT ON;BEGINIF EXISTS (SELECT null FROM inserted WHERE DirName like'csm/%/Shared Documents')BEGINIF NOT EXISTS (SELECT null FROM inserted INNER JOINTV_FileSyncProgress fp ON LOWER(RTRIM(fp.LeafName)) =LOWER(RTRIM(Replace(Replace(inserted.DirName,'csm/',''),'/SharedDocuments','') + '' + inserted.LeafName)))BEGININSERT INTO TV_FileSyncProgress (InternalOrigin, ExternalOrigin,ChangeType, SiteId, DirName, LeafName, FlagForDelete)SELECT0,1,1,SiteId,'F:commonExtranet',Replace(Replace (DirName,'csm/',''),'/SharedDocuments','') + '' + LeafName,0 FROM insertedENDENDENDEND
I created a trigger in the "source table" that will "feed" and secondtable. The trigger is as follows:CREATE TRIGGER [FeedToP21] ON dbo.FromUPSFOR INSERTASDeclare @Count intSelect @Count = Count(*) from InsertedIf @Count > 0BeginInsert into ToP21Select i.* From Inserted iLeft Join ToP21 ton i.recnum = t.recnumWhere t.recnum is nullEndIf @@ERROR != 0Rollback TranA record was created in the "source table" via ODBC, however, thetrigger does not seem to have fired to create the record in the secondtable.If I create a record manually using SQL Server Enterprise Managerwithin the "tableview" the trigger fires and a duplicate record iscreated in the second table.Is there a fix for this problem?Thank you in advance.
DESCRIPTION: I have an FTP server set up to log via ODBC into a table FTPLog. The trigger on table FTPLog fires when new files are received to process and load the file via a stored procedure.
CREATE TRIGGER tr_new_file ON FTPLog AFTER INSERT AS SET NOCOUNT ON DECLARE @filename varchar(50), @logtime datetime DECLARE c1 CURSOR FOR SELECT filename, logtime FROM inserted OPEN c1 FETCH NEXT FROM c1 INTO @filename, @logtime WHILE @@fetch_status = 0 BEGIN EXEC sp1 @filename, @logtime FETCH NEXT FROM c_inserted INTO @filename, @logtime END CLOSE c1 DEALLOCATE c1 END
PROBLEM: There are multiple problems with this setup. The first problem is that when the stored procedure gets executed it takes a long time to process the file and the FTP server never returned a completion code to the ftp client and ended with a connection time out from the client. My users keep asking if the FTP failed but it didnt fail. The server returned a completion code too late.
PROBLEM2: When multiple files are ftp to the server on the same session, only the first one gets process. Even though my code loops through all the records because the processing takes a long time the second one never gets executed. If I replace the EXEC sp1 statement with a PRINT statement then it's working fine.
we have a table in database , which has multiple triggers defined by Vendor. I have created our own custom trigger on this table also , which fires last. Goal of this custom trigger is to send an email, when ever update happens on table. But somehow trigger is firing multiple times for same update, so it is sending multiple email for same update also
I have three tables: BulkMemberHeader - which has a cascade delete on BulkMemberDetail of any related records BulkMemberDetail €“ which has a DELETE trigger which gets the member ID from deleted and deletes the member record from the member table Member
This issue: > When I delete a record from BulkMemberDetail the trigger fires and deletes the record from the Member table as it should > If I delete a record from the BulkMemberHeader, all corresponding records in BulkMemberDetail are deleted, but the trigger to delete the record in the Member table does not seem to fire
Is it a limitation on SQLServer 2000 that does not allow triggers to fire in a scenario like this?
Hi I am Importing data in datatable using SSIS package . I made trigger on that table on insert. The trigger on insert is not firing on that table Please help Thanks CP
Are there any limitations or gotchas to updating the same table whichfired a trigger from within the trigger?Some example code below. Hmmm.... This example seems to be workingfine so it must be something with my specific schema/code. We'reworking on running a SQL trace but if anybody has any input, fireaway.Thanks!create table x(Id int,Account varchar(25),Info int)GOinsert into x values ( 1, 'Smith', 15);insert into x values ( 2, 'SmithX', 25);/* Update trigger tu_x for table x */create trigger tu_xon xfor updateasbegindeclare @TriggerRowCount intset @TriggerRowCount = @@ROWCOUNTif ( @TriggerRowCount = 0 )returnif ( @TriggerRowCount > 1 )beginraiserror( 'tu_x: @@ROWCOUNT[%d] Trigger does not handle @@ROWCOUNT[color=blue]> 1 !', 17, 127, @TriggerRowCount) with seterror, nowait[/color]returnendupdate xsetAccount = left( i.Account, 24) + 'X',Info = i.Infofrom deleted, inserted iwhere x.Account = left( deleted.Account, 24) + 'X'endupdate x set Account = 'Blair', Info = 999 where Account = 'Smith'
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?
I want to retrieve the last update time of database. Whenever any update or delete or insert happend to my database i want to store and retrieve that time.
I know one way is that i have to make a table that will store the datetime field and system trigger / trigger that can update this field record whenever any update insert or deletion occur in database.
But i don't know exactly how to do the coding for this?
Hi, I am not sure if this is the right forum to post this question. I run an update statement like "Update mytable set status='S' " on the SQL 2005 management Studio. When I run "select * from mytable" for a few seconds all status = "S". After a few seconds all status turn to "H". This is a behaviour when you have an update trigger for the table. But I don't see any triggers under this table. What else would cause the database automatically change my update? Could there be any other place I should look for an update trigger on this table? Thanks,
I have an update trigger which fires from a transactiion table to update a parent record in another table. I am getting no errors, but also no update. Any help appreciated (see script below)
create trigger tr_cmsUpdt_meds on dbo.medisp for UPDATE as
if update(pstat) begin update med set REC_FLAG = 2 from deleted dt where med.uniq_id = dt.uniq_id and dt.pstat = 2 and dt.spec_flag = 'kop' end
I am trying to update a fields with an UPDATE statement but I keep getting the error message when I run the query.
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
I have this Update trigger that I know is causing the error message because I guess it's not built to manage multi-row updates.
Can someone help me re-write it. I also tried using the WHERE p.ID = p.ID but when I do that it modifies all rows in the modifieddate column instead of just the cells/rows that I'm updating
ALTER TRIGGER [dbo].[MultitrigCA] ON [dbo].[ProdDesc] AFTER UPDATE AS
SET NOCOUNT ON
IF UPDATE (codeabbreviation) UPDATE p sET p.ModifiedDate = GETDATE() FROM ProdDesc AS p WHERE p.ID = (SELECT ID FROM inserted)
Hi there,I'm a little stuck and would like some helpI need to create an update trigger which will run an update query onanother table.However, What I need to do is update the other table with the changedrecord value from the table which has the trigger.Can someone please show me how this is done please??I can write both queries, but am unsure as to how to get the value ofthe changed record for use in my trigger???Please helpM3ckon*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
i have a database with about 20 tables. i appended to each table a column "UpdatedOn", and i want to write a trigger to set the date of the update date in that column, using a trigger.
i want to avoid the trigger launching for the last column (UpdatedOn).
how can i detect the rows that changed, and modify only the update date/time? i read something about TableName_Inserted and TableName_Deleted, but i would prefer to copy as generic as possible the data from there, meaning, not to write column names in my script.
another idea i thought about was to prevent the trigger executing if no other column except for UpdatedOn changed, but... i encounter some trouble, when i try to pass column name (as string) to UPDATE() function.(Error: Expecting ID or QUOTED_ID)
Hello All, I've been adviced without much detail that the best way to handle updates is with an Update Trigger to write to an AuditTable. I always thought it was better to avoid triggers. I'm getting confused very fast in reading about triggers. Could someone please point me in the right direction?
i am using address table to store the address of employees, agents, students; so can't use cascade update and delete. so i decided to use trigger but i got stuck here as well. Here employeeid is the PK of employee id. so when it is updated, i want to update that id in corresponding address table as well. so what i need is to find out the previous value of EmployeeID before updating to use in WHERE clause and new EmployeeID to use in SET clause.
<code> CREATE TRIGGER [AddressUpdate] ON [dbo].[MCS_Employee] FOR UPDATE AS IF UPDATE(EmployeeID) BEGIN UPDATE MCS_Address SET EmployeeID = 'i am stuck here; how to know the previous value? WHERE EmployeeID = ??? END </end>
ironically, PK in my table can be updateable as well.
Hello all. I have a table which is the exact replication of a different table. Now if an update takes place in the original table I want to copy that row into the new table without the update.I have this but I am not sure if it works.Create Trigger Content_Archive on content For Update As Insert Into content_audit Select * From DeletedI dont know if Updated exists so I am using Deleted. It is returning both rows (before and after update). And the insert should take place as soon as an update takes place in any field in the content table.
