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

Help with update trigger Expand / Collapse
Author
Message
Posted Tuesday, January 15, 2008 2:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 8, 2012 5:56 AM
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
Post #442866
Posted Tuesday, January 15, 2008 3:36 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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
Post #442880
Posted Tuesday, January 15, 2008 3:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:34 AM
Points: 39,973, Visits: 36,330
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 2008, MVP
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

Post #442884
Posted Tuesday, January 15, 2008 4:03 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 8, 2012 5:56 AM
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.
Post #442888
Posted Tuesday, January 15, 2008 4:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585
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




Andras Belokosztolszki, MCPD, PhD
GoldenGate Software
Post #442889
Posted Tuesday, January 15, 2008 4:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 8, 2012 5:56 AM
Points: 40, Visits: 277
Thanks,

I'll give this a try.
Post #442890
Posted Tuesday, January 15, 2008 4:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 2:34 AM
Points: 39,973, Visits: 36,330
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 2008, MVP
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

Post #442892
Posted Monday, February 4, 2008 7:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, November 8, 2012 5:56 AM
Points: 40, Visits: 277
Hi Andras,

I modified your code slightly and it worked.

Thanks,
Iain.
Post #451156
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse