December 19, 2012 at 3:17 am
Hi,
Below is my table structure..
USE [SalesOptimizer_New]
GO
/****** Object: Table [dbo].[AutopilotCriteriasInUse] Script Date: 12/19/2012 15:44:50 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE 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]
GO
ALTER TABLE [dbo].[AutopilotCriteriasInUse] WITH NOCHECK ADD CONSTRAINT [FK_AutopilotCriteriasInUse_Autopilots] FOREIGN KEY([AutopilotID])
REFERENCES [dbo].[Autopilot] ([AutopilotID])
GO
ALTER TABLE [dbo].[AutopilotCriteriasInUse] CHECK CONSTRAINT [FK_AutopilotCriteriasInUse_Autopilots]
GO
ALTER TABLE [dbo].[AutopilotCriteriasInUse] ADD CONSTRAINT [DF_AutopilotCriteriasInUse_DateCreated] DEFAULT (getdate()) FOR [DateCreated]
GO
ALTER 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 TRANSACTION
GO
--Checking for already running created trigger
IF EXISTS ( SELECT *
FROM sys.triggers
WHERE object_id = OBJECT_ID(N'[dbo].[AutopilotCriteriasInUseInsertUpdate]') )
DROP TRIGGER [dbo].[AutopilotCriteriasInUseInsertUpdate]
GO
CREATE TRIGGER AutopilotCriteriasInUseInsertUpdate ON AutopilotCriteriasInUse
AFTER INSERT, UPDATE
AS
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)
END
GO
IF @@ERROR <> 0
AND @@TRANCOUNT > 0
ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
GO
Please help..
December 19, 2012 at 3:22 am
@@RowCount only looks at the last statement which in this case would be the DECLARE which will always return 0 rows
Use 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.
December 19, 2012 at 3:37 am
Thats not the issue,
The issue is in trigger update statment..
December 19, 2012 at 3:39 am
And which update statement within the trigger?
December 19, 2012 at 3:42 am
anthony.green (12/19/2012)
@@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.
Actually the declare statement doesn't effect the value of @@rowcount. See the code bellow that shows that.
select * from sys.objects
declare @i int
select @@rowcount
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
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 19, 2012 at 3:50 am
Adi Cohn-120898 (12/19/2012)
anthony.green (12/19/2012)
@@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.
Actually the declare statement doesn't effect the value of @@rowcount. See the code bellow that shows that.
select * from sys.objects
declare @i int
select @@rowcount
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
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.
December 19, 2012 at 4:10 am
Actually i need to update DateUpdated column whenever i will make changes in row but there is no primary key in table
and DateCreated column whenever any insertion will occure
Error:The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (2 rows).
December 19, 2012 at 4:49 am
I 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:
use tempdb
go
--Creating the table
create table Demo (i int, j int, TimeUpdated datetime)
go
--Creating the trigger
create trigger DocumentUpdateTime
on Demo
for update, insert
as
--Doing an update without using varibles.
--More recommended because the update
--can effect many rows
update Demo
set TimeUpdated = getdate()
from Demo inner join Inserted
on Demo.i = Inserted.i and Demo.j = Inserted.j
go
insert into Demo (i, j)
select 1,1
union all
select 1,1
union all
select 1,2
go
update Demo set j = 4 where j = 2
go
waitfor delay '00:00:01'
update Demo set j = 3 where j = 1
go
--Notice that the value of TimeUpdated
select * from Demo
update Demo set j = 1
--Notice that again it modified the column
--TimeUpdated. If I was using varibles,
--it would update some of the records
select * from Demo
go
drop table Demo
By the way – why don't you have a primary key? You should have one.
Adi
--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
December 19, 2012 at 5:05 am
Thank 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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply