Trigger or Stored Procedure Question

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 2005 Forums

Transact-SQL (2005)

Trigger or Stored Procedure Question

Author

Topic

mohanjohn
Starting Member

3 Posts

Posted - 2008-01-16 : 09:44:02

Persons Table
PersonID int NOT NULL PRIMARY KEY,
PersonFatherID int NULL FOREIGN KEY Persons(PersonID)
PersonMotherID int NULL FOREIGN KEY Persons(PersonID)
PersonGeneration int NULL
PersonFirstName nchar(20) NOT NULL
PersonLastName nchar(20) NOT NULL

Spouses Table
SpouseID int NOT NULL PRIMARY KEY
HusbandID int NOT NULL FOREIGN KEY Persons(PersonID)
WifeID int NOT NULL FOREIGN KEY Persons(PersonID)

Persons Table Data
PersonID PersonFatherID PersonMotherID PersonGeneration PersonFirstName PersonLastName
1 1 1 1 Adam Smith
2 2 2 1 Evelyn Smith
3 1 2 2 Caleb Smith
4 NULL NULL 0 Sara Jones

Spouses Table Data
SpouseID HusbandID WifeID
1 1 2
2 3 4

I want to update PersonGeneration column in Persons table when that person is added into Spouses table. For example, if Sara Jones is added as wife to Caleb Smith in Spouses table, then it should update Sara's PersonGeneration (0) with Caleb's PersonGeneration (2) in Persons table. I am thinking about a trigger or a stored procedure or both. Any help would be appreciated.

Thanks,
Mohan John

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-16 : 10:12:06

You can use trigger like this

CREATE TRIGGER MyTrig ON Spouses
AFTER INSERT
AS
UPDATE p
SET p.PersonGeneration =p2.PersonGeneration
FROM Persons p
INNER JOIN INSERTED i
ON i.WifeID=p.PersonID
INNER JOIN Persons p2
ON p2.PersonID=i.HusbandID
GO

mohanjohn
Starting Member

3 Posts

Posted - 2008-01-16 : 11:15:03

quote:

Originally posted by visakh16

You can use trigger like this

CREATE TRIGGER MyTrig ON Spouses
AFTER INSERT
AS
UPDATE p
SET p.PersonGeneration =p2.PersonGeneration
FROM Persons p
INNER JOIN INSERTED i
ON i.WifeID=p.PersonID
INNER JOIN Persons p2
ON p2.PersonID=i.HusbandID
GO

Thanks, I will try this.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2008-01-16 : 22:52:50

Personally I would lock down direct access to the tables and use a SP for all operations. Triggers cause all sorts of side effects (as programmed of course) that are not always obvious or desireable. Having an API approach is more clear. You will get arguments both ways, but I find the unexpected side effects of triggers can cause more confusion than a clearly defined API.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources