InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 06:39:45 GMT20http://www.sqlservercentral.com/Forums/Topic783116-392-1.aspxYou're welcome.Fri, 04 Sep 2009 13:25:41 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic783116-392-1.aspxYou really answered all my questions and shed some light.Thanks a bunch for the efforts :)Fri, 04 Sep 2009 13:04:57 GMTDaniel Chttp://www.sqlservercentral.com/Forums/Topic783116-392-1.aspx[quote][b]Daniel C (9/4/2009)[/b][hr]Also a question about this trigger. Is it acting like an AFTER trigger, generating an additional IO for each DML or is it doing the changes in the buffer and applying them after it finished execution?If it's an after trigger, I'm not gonna recommend to the developer due to the fact it might create problems in the long run - additional IO requests just for a simple update on DateModified.[/quote]Triggers get incorporated into the transaction that causes them to be fired off. Test it, of course, but it shouldn't cause any significant increase in I/O over the original transaction.Fri, 04 Sep 2009 12:59:27 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic783116-392-1.aspxThe main objection to any trigger is that it's hidden from the developers. A more standard way to update that column would be to make sure the update proc includes it in the definition, and then make sure no updates are done to the table except through that proc. Then the code isn't hidden from anyone. Of course, it's only hidden from devs who don't really know their business, but that's an unfortunately high percentage.The other reason to not use such a trigger is that the whole "DateUpdated" column is, in most cases, close to useless.If it's being used to determine whether the data should be archived or not, then it's useful. If it's being used as part of an audit trail, it's useless. (Just like the common "UpdatedBy" column is useless.)Other than those two things, no, such triggers are pretty common, and can be considered a standard practice.Fri, 04 Sep 2009 12:54:05 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic783116-392-1.aspxAlso a question about this trigger. Is it acting like an AFTER trigger, generating an additional IO for each DML or is it doing the changes in the buffer and applying them after it finished execution?If it's an after trigger, I'm not gonna recommend to the developer due to the fact it might create problems in the long run - additional IO requests just for a simple update on DateModified.Fri, 04 Sep 2009 12:54:01 GMTDaniel Chttp://www.sqlservercentral.com/Forums/Topic783116-392-1.aspxNevermind my previous question, I just realized that I'm using INSTEAD OF when you were using FOR UPDATE.I corrected the trigger definition, tested it and it seems to work.Any reasons NOT to have such a trigger on production?Fri, 04 Sep 2009 12:39:37 GMTDaniel Chttp://www.sqlservercentral.com/Forums/Topic783116-392-1.aspxThat's why my version of the trigger is "For Update", not "Instead of Update". That's one of the key differences.Fri, 04 Sep 2009 12:38:12 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic783116-392-1.aspxOk, that I must be doing something wrong, because remember getting through this variant of the trigger and it doesn't do what it supposed to do.Here is the table:[quote]CREATE TABLE [dbo].[Employees]( [DateCreated] [datetime] NULL, [DateModified] [datetime] NULL, [Id] [int] IDENTITY(1,1) NOT NULL, [FirstName] [nvarchar](100) NULL, [LastName] [nvarchar](100) NULL, [DeptIdt] [int] NOT NULL, CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED ( [Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GOALTER TABLE [dbo].[Employees] WITH CHECK ADD CONSTRAINT [FK_Employees_Departments] FOREIGN KEY([DeptIdt])REFERENCES [dbo].[Departments] ([Id])GOALTER TABLE [dbo].[Employees] CHECK CONSTRAINT [FK_Employees_Departments]GO[/quote]The entire data set is really simple, for exemplification purposes:[quote]DateCreated DateModified Id FirstName LastName DeptIdt2009-09-04 11:23:48.460 2009-09-04 11:23:48.460 8 John Doe 12009-09-04 11:23:48.460 2009-09-04 11:23:48.460 9 Marry Doe 22009-09-04 11:23:48.460 2009-09-04 11:23:48.460 10 John Long Silver 32009-09-04 11:23:48.460 2009-09-04 11:23:48.460 11 Ted King 2[/quote]The trigger is in place and looking like this:[quote]IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[trg_UpdateDateModified]'))DROP TRIGGER [dbo].[trg_UpdateDateModified]GOCREATE TRIGGER [dbo].[trg_UpdateDateModified]ON [dbo].[Employees]INSTEAD OF UPDATEASBEGIN UPDATE Employees SET DateModified = getdate() FROM Employees INNER JOIN inserted ON Employees.ID = inserted.ID;ENDGO[/quote]I run the following queries:[quote]select * from Employeesupdate Employees SET FirstName = 'Johnny' where Id=8select * From Employees[/quote]and finally, here are the results:[quote]BEFORE update[b]2009-09-04 11:23:48.460 2009-09-04 11:23:48.460 8 John Doe 1[/b]2009-09-04 11:23:48.460 2009-09-04 11:23:48.460 9 Marry Doe 22009-09-04 11:23:48.460 2009-09-04 11:23:48.460 10 John Long Silver 32009-09-04 11:23:48.460 2009-09-04 11:23:48.460 11 Ted King 2AFTER update:[b]2009-09-04 11:23:48.460 2009-09-04 11:28:28.813 8 John Doe 1[/b]2009-09-04 11:23:48.460 2009-09-04 11:23:48.460 9 Marry Doe 22009-09-04 11:23:48.460 2009-09-04 11:23:48.460 10 John Long Silver 32009-09-04 11:23:48.460 2009-09-04 11:23:48.460 11 Ted King 2[/quote]As you can see, the DateModified is updated, but NOT the FirstName column. Am I missing something here?Fri, 04 Sep 2009 12:35:15 GMTDaniel Chttp://www.sqlservercentral.com/Forums/Topic783116-392-1.aspxDoesn't matter if the DML has the ID column in it or not. Triggers have two "tables", one called "inserted", one called "deleted". The inserted one is a snapshot of each row that was either inserted or updated. The deleted one is a snapshot of each row that was deleted, or each row that was updated, with the pre-update data. In other words, inserted = after, deleted = before, for updates.Fri, 04 Sep 2009 12:16:23 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic783116-392-1.aspxI'll try it in a sec, but I couldn't help it to ask, where's that inserted.ID coming from? Yes, I do have a PK in the table, but if the triggering DML statement doesn't involve any PK, how's that going to help?Fri, 04 Sep 2009 12:11:19 GMTDaniel Chttp://www.sqlservercentral.com/Forums/Topic783116-392-1.aspxTry this:[code]CREATE TRIGGER trg_UpdateDateModifiedON EmployeesFOR UPDATEASUPDATE EmployeesSET DateModified = getdate()FROM EmployeesINNER JOIN inserted on Employees.ID = inserted.ID;[/code]I'm assuming the existence of an ID column in the Employees table. You'll need to modify it to use the correct primary key in the join if ID isn't what you're using.Fri, 04 Sep 2009 12:04:33 GMTGSquaredhttp://www.sqlservercentral.com/Forums/Topic783116-392-1.aspxHowdy folks,I just realized I'm disappointed with sql server 2008's trigger capabilities because I was not quite able to implement one of the developer's request.I need to create a trigger that updates a DateModifed column for a given set of rows that are being updated by a DML statement. After some internet research, I came up with something like this:CREATE TRIGGER trg_UpdateDateModifiedON EmployeesINSTEAD OF UPDATEASBEGIN IF UPDATE(FirstName) UPDATE E SET E.DateModified = GETDATE(), E.FirstName = I.FirstName FROM Employees E, inserted I WHERE E.FirstName=I.FirstNameENDGOI see some huge problems with this:- The trigger will fire only when FirstName column will be targeted with an update. If I want the trigger to fire for any columne that will be targeted with an update, I'll have to implement conditions within the trigger for every single column part of the table - that's kind of unacceptable;- Conversely, if I create a generic trigger that fires for any update, without to specify the column in IF UPDATE(column) I will not be able to update the DateModified rows that were targeted by the triggering DML statement in the first place. The result would be that all the DateModified entries in the whole table would be updated;- Assuming I choose to go down on the path of creating the trigger condition for every single column in the table, the update statement inside trigger is totally inefficient because it will have to use indexes to find out which rows have been updated (it's an update join as you can see) so for no reason on earth I will create 1 index for each column in the table.- Even so, the trigger will yield improper results when the inside update-join will affect existing rows in the table that WERE not targeted by triggering DML statement. I've seen it with my own eyes in the ensuing tests.- Assuming that I'm still nuts enough to implement the above solution, I'll have to write triggers like this for every single freaking table and column in the db; the db is supposed to replicate in large lines whatever is in a old mysql db, which as y'all know has one of the few nifty things found in mysql: `date_modified` timestamp NOT NULL default CURRENT_TIMESTAMP [b]on update CURRENT_TIMESTAMP[/b]To wrap up things, in Oracle the above state trigger would look absolutely simple and elegant, like this:CREATE OR REPLACE TRIGGER trg_UpdateDateModifiedBEFORE INSERT OR UPDATEON EmployeesFOR EACH ROWDECLAREBEGINIF INSERTING THEN :NEW.DateCreated := SYSDATE; :NEW.DateModified := SYSDATE;END IF;IF UPDATING THEN :NEW.DateModified := SYSDATE;END IF;EXCEPTION WHEN OTHERS THEN RAISE_APPLICATION_ERROR(-20101, 'Error : '||SQLCODE||' - '||SQLERRM);END;So, I dare you to solve this one.Fri, 04 Sep 2009 11:50:13 GMTDaniel C