Create A trigger for update - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Create A trigger for update

Author

Topic

iamsmahd
Yak Posting Veteran

55 Posts

Posted - 2004-11-30 : 00:43:49

Hi all
I wanna to create a trigger for updating a table with below conditions:

If value of field “Seq” for updated record has got different value of “Seq” before updating, rollback transaction and return

also I have written this code for that but unfortunately it does not work ...what is the problem?
::::::::::::::::::::::::::::::::::

CREATE TRIGGER PadakhtCheck_Update ON [dbo].[Dpc_PardakhtCheck]
FOR UPDATE

AS

declare @hsU as tinyint
declare @hsO as tinyint
select @hsU=i.hseq from inserted i inner join dpc_pardakhtcheck dc on i.resid_no=dc.resid_no
and i.cyear=dc.cyear

select @hsO=dc.hseq from inserted i inner join dpc_pardakhtcheck dc on i.resid_no=dc.resid_no
and i.cyear=dc.cyear

if @hsU<>@hsO
begin
raiserror('lfkgjf',16,1)
rollback transaction
end

Thank you very much

sonia

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-11-30 : 01:19:40

Assuming you are only updating one record at a time....try this...


CREATE TRIGGER PadakhtCheck_Update ON [dbo].[Dpc_PardakhtCheck] FOR UPDATE
AS

   declare @hsU as tinyint
   declare @hsO as tinyint
   select @hsU=hseq from inserted 

   select @hsO=hseq from deleted

   if @hsU<>@hsO
   begin
      raiserror('lfkgjf',16,1)
      rollback transaction
   end

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

iamsmahd
Yak Posting Veteran

55 Posts

Posted - 2004-12-01 : 02:04:09

hi....
thank you very much........but if i wanna to update more than one record at a time what will be the solution for trigger?

thanks again

sonia

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-01 : 02:24:46

if update(hseq)
begin
--raiserror
--rollback transaction
end

--------------------
keeping it simple...

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-12-01 : 03:30:41

quote:
Originally posted by jen

if update(hseq)
begin
--raiserror
--rollback transaction
end

Jen, This will cause it to fail even if "hseq" is updated with the same value.

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-01 : 03:31:29

if you change it with the same value it's not an update right?

--------------------
keeping it simple...

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-12-01 : 03:35:11

quote:
Originally posted by iamsmahd
thank you very much........but if i wanna to update more than one record at a time what will be the solution for trigger?

Slightly altered (This will work for both single and multiple record updates)


CREATE TRIGGER PadakhtCheck_Update ON [dbo].[Dpc_PardakhtCheck] FOR UPDATE
AS

   If exists (SELECT 'x' FROM inserted I, deleted D 
              WHERE I.HSEQ <> D.HSEQ and I.<primary-key> = D.<primary-key>
   begin
      raiserror('lfkgjf',16,1)
      rollback transaction
   end

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

hgorijal
Constraint Violating Yak Guru

277 Posts

Posted - 2004-12-01 : 03:38:58

quote:
Originally posted by jen

if you change it with the same value it's not an update right?

--------------------
keeping it simple...

It is. An update is a physical update of a column with a value, even if it is the same value/column

update(hseq) is true even in the below statement

 update table set hseq = hseq

Hemanth Gorijala
Exchange a Dollar, we still have ONE each._______Exchange an Idea, we both have TWO each.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-12-01 : 03:50:00

ok, so the update is being done as a row whether or not if the value have been changed. i assumed that when you say update, it was meant that the value was changed in comparison to the previous one.

won't it be easy if you just don't allow that field to be updated then? column level permission?

--------------------
keeping it simple...

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2004-12-04 : 16:34:48

What are the requirements when dealing with a multi-row update? Do we roll back the entire transaction if even one HSeq column changed? Or do we allow the "good" updates to proceed and reject only the "bad" ones? Or do we allow the "bad" ones to complete but fix the change to HSeq?

-- Fix the bad HSeq and let everything proceed

if update(hseq)
begin
update MyTable
set HSeq = d.HSeq -- Restore original value
from MyTable t
join inserted i on i.PK = t.PK
join deleted d on d.PK = t.PK
where i.HSeq <> d.HSeq
end

HTH

=========================================
Let X = {All sets s such that s is not an element of s}

(X element of X) ==> (X not element of X)
(X not element of X) ==> (X element of X)

(Bertrand Russell Paradox)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources