January 15, 2008 at 2:48 am
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
January 15, 2008 at 3:36 am
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
January 15, 2008 at 3:47 am
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
January 15, 2008 at 4:03 am
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.
January 15, 2008 at 4:04 am
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 :). 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
January 15, 2008 at 4:05 am
Thanks,
I'll give this a try.
January 15, 2008 at 4:08 am
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
February 4, 2008 at 7:55 am
Hi Andras,
I modified your code slightly and it worked.
Thanks,
Iain.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply