InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 18 Jun 2013 20:35:00 GMT20http://www.sqlservercentral.com/Forums/Topic1398257-391-1.aspxThank you Adi,I appreciate your help. I will look into my code and change as per your code.In case of error, i will contact you.Thanks for help.Aadhar.Wed, 19 Dec 2012 05:05:15 GMTAadhar Joshihttp://www.sqlservercentral.com/Forums/Topic1398257-391-1.aspxI don't think that the error that you got is an error that is generated from SQL Server. I couldn't find an error with similar text and you didn't supply an error number. In any case, you can use a trigger to update table that has no primary key from that trigger update one or few records from the same table. The code bellow does that:[code]use tempdbgo--Creating the tablecreate table Demo (i int, j int, TimeUpdated datetime)go--Creating the triggercreate trigger DocumentUpdateTimeon Demofor update, insertas--Doing an update without using varibles.--More recommended because the update--can effect many rowsupdate Demoset TimeUpdated = getdate()from Demo inner join Insertedon Demo.i = Inserted.i and Demo.j = Inserted.jgoinsert into Demo (i, j)select 1,1union allselect 1,1union allselect 1,2goupdate Demo set j = 4 where j = 2gowaitfor delay '00:00:01'update Demo set j = 3 where j = 1go--Notice that the value of TimeUpdatedselect * from Demoupdate Demo set j = 1--Notice that again it modified the column--TimeUpdated. If I was using varibles,--it would update some of the recordsselect * from Demogodrop table Demo[/code]By the way – why don't you have a primary key? You should have one.AdiWed, 19 Dec 2012 04:49:09 GMTAdi Cohn-120898http://www.sqlservercentral.com/Forums/Topic1398257-391-1.aspxActually i need to update DateUpdated column whenever i will make changes in row but there is no primary key in tableand DateCreated column whenever any insertion will occureError:The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (2 rows).Wed, 19 Dec 2012 04:10:15 GMTAadhar Joshihttp://www.sqlservercentral.com/Forums/Topic1398257-391-1.aspx[quote][b]Adi Cohn-120898 (12/19/2012)[/b][hr][quote][b]anthony.green (12/19/2012)[/b][hr]@@RowCount only looks at the last statement which in this case would be the DECLARE which will always return 0 rowsUse COUNT(*) from the INSERTED table instead.But if the trigger is after an insert or update, then you will always have something in the inserted / deleted tables so the need to check wouldn't matter and you wouldnt need the rollback, as something as been inserted or updated.[/quote]Actually the declare statement doesn't effect the value of @@rowcount. See the code bellow that shows that.[code]select * from sys.objectsdeclare @i intselect @@rowcount[/code] As for the original question – pleas specify what is not working? Do you get an error message? Does it seems to work but nothing gets modifies? Do you see modifications in the data but not the one that you expected? Also take into consideration that you wrote a trigger that most chances won't work correct in case that more then 1 record will be modified (I say most chances because you didn't specify what you are trying to do and what actually happens)Adi [/quote]I stand corrected, always thought it was at the scope of the last statement, hence the delcare.Also why not just join back to the inserted or deleted tables instead of pulling out the data into variables and then doing the update? That way it is multi-row incase the update or insert more than 1 row at a time. Otherwise you would need to loop through the inserted/deleted tables row by row pulling out the next value.Wed, 19 Dec 2012 03:50:14 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1398257-391-1.aspx[quote][b]anthony.green (12/19/2012)[/b][hr]@@RowCount only looks at the last statement which in this case would be the DECLARE which will always return 0 rowsUse COUNT(*) from the INSERTED table instead.But if the trigger is after an insert or update, then you will always have something in the inserted / deleted tables so the need to check wouldn't matter and you wouldnt need the rollback, as something as been inserted or updated.[/quote]Actually the declare statement doesn't effect the value of @@rowcount. See the code bellow that shows that.[code]select * from sys.objectsdeclare @i intselect @@rowcount[/code] As for the original question – pleas specify what is not working? Do you get an error message? Does it seems to work but nothing gets modifies? Do you see modifications in the data but not the one that you expected? Also take into consideration that you wrote a trigger that most chances won't work correct in case that more then 1 record will be modified (I say most chances because you didn't specify what you are trying to do and what actually happens)Adi Wed, 19 Dec 2012 03:42:05 GMTAdi Cohn-120898http://www.sqlservercentral.com/Forums/Topic1398257-391-1.aspxAnd which update statement within the trigger?Wed, 19 Dec 2012 03:39:06 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1398257-391-1.aspxThats not the issue,The issue is in trigger update statment..Wed, 19 Dec 2012 03:37:15 GMTAadhar Joshihttp://www.sqlservercentral.com/Forums/Topic1398257-391-1.aspx@@RowCount only looks at the last statement which in this case would be the DECLARE which will always return 0 rowsUse COUNT(*) from the INSERTED table instead.But if the trigger is after an insert or update, then you will always have something in the inserted / deleted tables so the need to check wouldn't matter and you wouldnt need the rollback, as something as been inserted or updated.Wed, 19 Dec 2012 03:22:52 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1398257-391-1.aspxHi,Below is my table structure..USE [SalesOptimizer_New]GO/****** Object: Table [dbo].[AutopilotCriteriasInUse] Script Date: 12/19/2012 15:44:50 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TABLE [dbo].[AutopilotCriteriasInUse]( [AutopilotID] [int] NOT NULL, [CriteriaID] [int] NOT NULL, [useForHitrate] [bit] NULL, [Ordinal] [tinyint] NULL, [useForSearch] [bit] NULL, [isOR] [bit] NULL, [DateCreated] [smalldatetime] NULL, [DateUpdated] [smalldatetime] NULL) ON [PRIMARY]GOALTER TABLE [dbo].[AutopilotCriteriasInUse] WITH NOCHECK ADD CONSTRAINT [FK_AutopilotCriteriasInUse_Autopilots] FOREIGN KEY([AutopilotID])REFERENCES [dbo].[Autopilot] ([AutopilotID])GOALTER TABLE [dbo].[AutopilotCriteriasInUse] CHECK CONSTRAINT [FK_AutopilotCriteriasInUse_Autopilots]GOALTER TABLE [dbo].[AutopilotCriteriasInUse] ADD CONSTRAINT [DF_AutopilotCriteriasInUse_DateCreated] DEFAULT (getdate()) FOR [DateCreated]GOALTER TABLE [dbo].[AutopilotCriteriasInUse] ADD CONSTRAINT [DF_AutopilotCriteriasInUse_DateUpdated] DEFAULT (getdate()) FOR [DateUpdated]GO------------------------------------I am creating update trigger but it is not working....................------------------------------------AutopilotCriteriasInUse------------------------------------BEGIN TRANSACTIONGO--Checking for already running created triggerIF EXISTS ( SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[AutopilotCriteriasInUseInsertUpdate]') ) DROP TRIGGER [dbo].[AutopilotCriteriasInUseInsertUpdate]GOCREATE TRIGGER AutopilotCriteriasInUseInsertUpdate ON AutopilotCriteriasInUse AFTER INSERT, UPDATEAS DECLARE @PnumPkid INT , @PDesc NVARCHAR(128) IF @@ROWCOUNT = 0 -- exit trigger when zero records affected BEGIN ROLLBACK TRANSACTION RETURN END ; DECLARE @AutopilotID INT DECLARE @CriteriaID INT DECLARE @useForHitrate BIT DECLARE @Ordinal TINYINT DECLARE @useForSearch BIT DECLARE @isOR BIT IF EXISTS ( SELECT * FROM INSERTED ) AND NOT EXISTS ( SELECT * FROM DELETED ) BEGIN SELECT @AutopilotID = AutopilotID , @CriteriaID = CriteriaID , @useForHitrate = useForHitrate , @Ordinal = Ordinal , @useForSearch = useForSearch , @isOR = isOR FROM INSERTED UPDATE dbo.AutopilotCriteriasInUse SET DateCreated = GETDATE() WHERE AutopilotID = @AutopilotID AND CriteriaID = @CriteriaID AND useForHitrate = @useForHitrate AND Ordinal = @Ordinal AND useForSearch = @useForSearch AND isOR = @isOR END IF EXISTS ( SELECT * FROM DELETED ) BEGIN SELECT @AutopilotID = AutopilotID , @CriteriaID = CriteriaID , @useForHitrate = useForHitrate , @Ordinal = Ordinal , @useForSearch = useForSearch , @isOR = isOR FROM DELETED UPDATE dbo.AutopilotCriteriasInUse SET DateUpdated = GETDATE() WHERE AutopilotID = @AutopilotID AND CriteriaID = @CriteriaID AND useForHitrate = @useForHitrate AND Ordinal = @Ordinal AND useForSearch = @useForSearch AND ISNULL(isOR,0) = ISNULL(@isOR,0) ENDGOIF @@ERROR <> 0 AND @@TRANCOUNT > 0 ROLLBACK TRANSACTIONGOIF @@TRANCOUNT > 0 COMMIT TRANSACTIONGOPlease help..Wed, 19 Dec 2012 03:17:05 GMTAadhar Joshi