Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update trigger without primary key Expand / Collapse
Author
Message
Posted Wednesday, December 19, 2012 3:17 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 3:40 AM
Points: 1,608, Visits: 381
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..
Post #1398257
Posted Wednesday, December 19, 2012 3:22 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
@@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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1398260
Posted Wednesday, December 19, 2012 3:37 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 3:40 AM
Points: 1,608, Visits: 381
Thats not the issue,
The issue is in trigger update statment..
Post #1398271
Posted Wednesday, December 19, 2012 3:39 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
And which update statement within the trigger?



Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1398272
Posted Wednesday, December 19, 2012 3:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 20, 2014 12:22 PM
Points: 2,125, Visits: 5,544
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 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.


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/
Post #1398273
Posted Wednesday, December 19, 2012 3:50 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Saturday, November 1, 2014 6:54 AM
Points: 5,221, Visits: 5,118
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 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.


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.




Want an answer fast? Try here
How to post data/code for the best help - Jeff Moden
Need a string splitter, try this - Jeff Moden
How to post performance problems - Gail Shaw
CrossTabs-Part1 & Part2 - Jeff Moden
SQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola Hallengren
Managing Transaction Logs - Gail Shaw
Troubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger

Post #1398276
Posted Wednesday, December 19, 2012 4:10 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 3:40 AM
Points: 1,608, Visits: 381
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).
Post #1398287
Posted Wednesday, December 19, 2012 4:49 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, November 20, 2014 12:22 PM
Points: 2,125, Visits: 5,544
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/
Post #1398311
Posted Wednesday, December 19, 2012 5:05 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 3:40 AM
Points: 1,608, Visits: 381
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.
Post #1398323
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse