Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Help with update trigger


Help with update trigger

Author
Message
icampbell
icampbell
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 277
I am new to sql server and would appreciate any assistance in creating an update trigger.

I have a view called

dbo.view_badge which contains the following columns

Year, stu_code, join_code, forename1, forename2, surname, dob, course, end_date

I have a table called

dbo.t_badge which contains the same columns

Year, stu_code, join_code, forename1, forename2, surname, dob, course, end_date

How do i create an update trigger which will update the table dbo.t_badge when any changes are made to the view

dbo.view_badge.

I would appreciate any assistance as i don't have any experience with triggers.

Thanks,
Iain
Andras Belokosztolszki
Andras Belokosztolszki
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1387 Visits: 1585
Hi, a very basic trigger you could use a starting point is:


CREATE TRIGGER dbo.view_badge_upd ON dbo.view_badge
INSTEAD OF UPDATE
AS
IF @@rowcount = 0
RETURN
IF ( SELECT COUNT(*)
FROM Inserted
) > 1
BEGIN
PRINT 'Only one row at a time can be modified'
RETURN
END
IF UPDATE(year)
OR UPDATE(stu_code)
BEGIN
RAISERROR ( 'cannot change the year or the stu_code', 16, 1 )
ROLLBACK TRAN
RETURN
END


UPDATE dbo.t_badge
SET forename1 = i.forename1
, forename2 = i.forename2
, surname = i.surname
, dob = i.dob
, course = i.course
, end_date = i.end_date
FROM inserted i
WHERE i.year = t_badge.year
AND i.stu_code = t_badge.stu_code

RETURN


Note that I assumed that year and stu_code are the primary keys (I did not really look into the table definition you had)

The above will allow modifications to non-primary key columns, and only one row at a time.
Regards,
Andras



Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47402 Visits: 44399
If you update a view, the table underlying that view is updated. You shouldn't need a trigger.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


icampbell
icampbell
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 277
The table is created, then the data inserted from the view.
I have a seperate procedure that fires every minute that works for inserts.
However how do i do a triger for updates.
Andras Belokosztolszki
Andras Belokosztolszki
Ten Centuries
Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)Ten Centuries (1.4K reputation)

Group: General Forum Members
Points: 1387 Visits: 1585
GilaMonster (1/15/2008)
If you update a view, the table underlying that view is updated. You shouldn't need a trigger.


Gail is of course right Smile. If you just want to have an extra level of indirection, and let people modify the underlying table of your view, you can do it via the view updating. The trigger is useful more for introducing extra constraints, like what columns and in what way you allow to modify.

Regards,
Andras



Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
icampbell
icampbell
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 277
Thanks,

I'll give this a try.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47402 Visits: 44399
icampbell (1/15/2008)
The table is created, then the data inserted from the view.
I have a seperate procedure that fires every minute that works for inserts.
However how do i do a triger for updates.


So the view is based on a different table than you want to update?

Then look at Andras's post. He details an instead of trigger, which is what you'll need.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


icampbell
icampbell
SSC Rookie
SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)SSC Rookie (40 reputation)

Group: General Forum Members
Points: 40 Visits: 277
Hi Andras,

I modified your code slightly and it worked.

Thanks,
Iain.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search