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
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?
hi,Here's the scenario1) I am running a DTS job to fetch some rows from Oracle2) The job populates the Table A as step 13) Then it fires a update statement which updates the rows in Table B.Here's the statementUPDATE Table B SETtime = case when (select median from Table A where sno = sno and TableA.stno=70 ) is null then timeelse (select median from Table A where Table B.sno = Table A.sno andTable A.sstno=70) end ,endWHERE EXISTS (select sstno from Table A where Table B.sno = TableA.sno)There is a trigger on table B which should fire as soon as thevalue>15.When I fire the update statement direcly with a higher value than itfires the trigger.update table B set time=17 where b.sno=1000But not when the job runs...I am puzzled.Thoughts?AJ
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?
I'm new to this whole SQL Server 2005 thing as well as database design and I've read up on various ways I can integrate business constraints into my database. I'm not sure which way applies to me, but I could use a helping hand in the right direction.
A quick explanation of the various tables I'm dealing with: WBS - the Work Breakdown Structure, for example: A - Widget 1, AA - Widget 1 Subsystem 1, and etc. Impacts - the Risk or Opportunity impacts for the weights of a part/assembly. (See Assemblies have Impacts below) Allocations - the review of the product in question, say Widget 1, in terms of various weight totals, including all parts. Example - September allocation, Initial Demo allocation, etc. Mostly used for weight history and trending Parts - There are hundreds of Parts which will eventually lead to thousands. Each part has a WBS element. [Seems redundant, but parts are managed in-house, and WBS elements are cross-company and issued by the Government] Parts have Allocations - For weight history and trending (see Allocations). Example, Nut 17 can have a September 1st allocation, a September 5th allocation, etc. Assemblies - Parts are assemblies by themselves and can belong to multiple assemblies. Now, there can be multiple parts on a product, say, an unmanned ground vehicle (UGV), and so those parts can belong to a higher "assembly" [For example, there can be 3 Nut 17's (lower assembly) on Widget 1 Subsystem 2 (higher assembly) and 4 more on Widget 1 Subsystem 5, etc.]. What I'm concerned about is ensuring that the weight roll-ups are accurate for all of the assemblies. Assemblies have Impacts - There is a risk and opportunity impact setup modeled into this design to allow for a risk or opportunity to be marked on a per-assembly level. That's all this table represents.
A part is allocated a weight and then assigned to an assembly. The Assemblies table holds this hierarchical information - the lower assembly and the higher one, both of which are Parts entries in the [Parts have Allocations] table.
Therefore, to ensure proper weight roll ups in the [Parts have Allocations] table on a per part-basis, I would like to check for any inserts, updates, deletes on both the [Parts have Allocations] table as well as the [Assemblies] table and then re-calculate the weight roll up for every assembly. Now, I'm not sure if this is a huge performance hog, but I do need to keep all the information as up-to-date and as accurate as possible. As such, I'm not sure which method is even correct, although it seems an AFTER DML trigger is in order (from what I've gathered thus far). Keep in mind, this trigger needs to go through and check every WBS or Part and then go through and check all of it's associated assemblies and then ensure the weights are correct by re-summing the weights listed.
If you need the design or create script (table layout), please let me know.
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?
need help with recursive trigger on multiple insert i continue from this code http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=3010163&SiteID=1 tnx for the help
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
I have a after update trigger that calculates one of the columns based on other column values. It has to be a trigger and only fires an update statement against the table itself when the column has changed. At a simple level it is doing something similar to the code below
IF UPDATE(Col1) update MyTable SET Col2 = Col1 WHERE Col2 <> Col1
It works everywhere except on one site where the trigger causes itself to recurse until it reaches the 32 level error. It can be fixed by checking whether there and any records in the inserted table at the top. Like so.
IF EXISTS (SELECT * FROM inserted) begin IF UPDATE(Col1) update MyTable SET Col2 = Col1 WHERE Col2 <> Col1 end
However, I would like to know whether there is some system setting other than "nested triggers"Â that I am missing that would cause the behaviour.
hello guysi am using a table that its secondary key connected to its primary key...and as sql server 2000 doesnt allow cascade delete fore such,i had to write a trigger myselfso i wrote the following triggerCREAT TRIGGER nameON tableFOR DeleteASBEGINIF @@ROWCOUNT >0Delete from table where table.parentID in (select sortID from deleted);ENDthen i went to the table and i tried to delete...and it gave me an error....that there are records that have there parentID= sortID of the table i am trieng to delete...so i deleted the relationship...and kept the triggerand now ...when i delete one...it deletes one level down....but not more....i mean when i delete sortID=4it deletes all the records that has parentID=4...and NOT more..whereas my aim was to have it recursive not to have records lost in my databasehope i explained good as much as i hope to find an answer soon...a clear one...and thanks in advanced...
Hi, Trying to update a single value within a table, thus eliminating nulls. Another words, if the value is NULL update it with the next preceeding non-null value. In this example, 1 should be CO, 2 should be CO, 6 should be CO, 8 should be TT, and 10 should be TT.
For example,
1 NULL 2 NULL 3 CO 4 CO 5 CO 6 NULL 7 TT 8 NULL 9 TT 10 NULL
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 am having problem to apply updates into this function below. I triedusing cursor for updates, etc. but no success. Sql server keeps tellingme that I cannot execute insert or update from inside a function and itgives me an option that I could write an extended stored procedure, butI don't have a clue of how to do it. To quickly fix the problem theonly solution left in my case is to convert this recursive functioninto one recursive stored procedure. However, I am facing one problem.How to convert the select command in this piece of code below into an"execute" by passing parameters and calling the sp recursively again.### piece of code ############SELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uid######### my function ###########CREATE FUNCTION Mkt_GetChildren(@uid int, @subtotal decimal ,@DateStart datetime, @DateEnd datetime)RETURNS decimalASBEGINIF EXISTS (SELECTuidFROMcategories WHEREParentID = @uid)BEGINDECLARE my_cursor CURSOR FORSELECT uid, classid5 FROM categories WHERE parentid = @uiddeclare @getclassid5 varchar(50), @getuid bigint, @calculate decimalOPEN my_cursorFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5WHILE @@FETCH_STATUS = 0BEGINFETCH NEXT FROM my_cursor INTO @getuid, @getclassid5select @calculate = dbo.Mkt_CalculateTotal(@getclassid5, @DateStart,@DateEnd)SET @subtotal = CONVERT (decimal (19,4),(@subtotal + @calculate))ENDCLOSE my_cursorDEALLOCATE my_cursorSELECT @subtotal = dbo.Mkt_GetChildren(uid, @subtotal,@DateStart, @DateEnd)FROM categories WHERE ParentID = @uidENDRETURN @subtotalENDGORod
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,
