Cascade update using trigger - 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)

Cascade update using trigger

Author

Topic

dve
Starting Member

19 Posts

Posted - 2005-09-08 : 08:06:41

Hi,

Considder this situation:

CREATE TABLE departments(id INT PRIMARY KEY, name NVARCHAR(150))
CREATE TABLE employees (id INT PRIMARY KEY, firstname NVARCHAR(150), lastname NVARCHAR(150), departmentID INT)

(employee.departmentID contains foreign keys to departments.id)

How could I write a trigger that would update the foreign keys when I update departments.id ? Like what a normal foreign key constraint would do when I specify 'on update cascade', which I can unfortunately not do...

Thank you!

Kristen
Test

22859 Posts

Posted - 2005-09-08 : 11:32:54

I don't see how you can - because you cannot know the relationship between "inserted" and "deleted" tables - unless there is another column which has a unique value and will not change.

If there was only one row in "inserted" I suppose you would be OK.

Bu maybe I'm being thick and someone smarter knows the answer!

Kristen

dve
Starting Member

19 Posts

Posted - 2005-09-08 : 12:09:24

I don't know but are the records copied into the "inserted" and "deleted" table in the same order?

If so you could use that an a cursor???? Does anyone know this?

Kristen
Test

22859 Posts

Posted - 2005-09-08 : 12:23:00

I don't reckon there's any reliable sequence to "inserted" / "deleted" - any more than there is to any other database table - but I don't know that for sure.

Some bright spark will be along in a minute!

Kristen

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-08 : 13:13:45

>> which I can unfortunately not do...
Do you have circular relationships ?

>> If there was only one row in "inserted" I suppose you would be OK
Yes.
You could allow single row updates to the id column, if multiple keys are updated raise an error.
Let the clients only do single row updates to the PK.

>> I don't know but are the records copied into the "inserted" and "deleted" table in the same order?
I wouldn't rely on it.

You cannot do what you are attempting without being able to identify the updated rows uniquely.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-08 : 13:14:18

So why can't you use cascade update at the constraint level?

Tara

dve
Starting Member

19 Posts

Posted - 2005-09-08 : 13:31:52

Because i'm having relationships that don't permit me to use DRI there and I can't do anything to work around it...

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-09-08 : 13:38:24

You have a design problem if you are updating the primary key. But anyway...how many tables would need to be updated? If it isn't too many, then you could handle this at the stored procedure level or in your application if you are using in-line SQL.

Tara

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-09-08 : 16:49:14

uuuuhhhhhmmmmm I thought it was impossible to change the value of a PK without dropping the constraint. Let me check...

CREATE TABLE MyTable
(
[id] int IDENTITY(1,1) PRIMARY KEY,
MyName varchar(10)
)

GO

INSERT INTO MyTable (MyName)
VALUES('sean')

go

update MyTable SET [id] = 2

==============================================

(1 row(s) affected)

Server: Msg 8102, Level 16, State 1, Line 1
Cannot update identity column 'id'.

Yep. I was right. Or am I missing something fundamental. I get a little slow at the end of the day.

As for the trigger thing...
~ "The order of data in the database has no meaning" - that Brett guy.

====================================================
Regards,
Sean Roussy

"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-08 : 16:56:48

>>uuuuhhhhhmmmmm I thought it was impossible to change the value of a PK without dropping the constraint.

Are we confusing identity column with primary key?

>>Server: Msg 8102, Level 16, State 1, Line 1
>>Cannot update identity column 'id'.


create table #temp (pk int primary key, i int)
insert #temp (pk,i) values (1,1)

select * from #temp
update #temp set pk = 2 where pk = 1
select * from #temp

drop table #temp

Be One with the Optimizer
TG

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-08 : 17:20:44

Well, yeah, ummhh
Didn't realize I was that sleep deprived...

I ran into the same problem a few months ago, and had to make the FK no check.
You know, so that it get's zebra-colored and is just there for esthetical purposes.
And wrote a bunch of trigger stuff, it all ended in a redesign

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-09-08 : 17:24:23

>> Are we confusing identity column with primary key?
It checks that error first... I think.

And sean is an identity.

>> Yep. I was right. Or am I missing something fundamental. I get a little slow at the end of the day.
And very very tired.
Probably just forgot about the FK !

Thrasymachus
Constraint Violating Yak Guru

483 Posts

Posted - 2005-09-08 : 20:52:54

whoops. sorry about that.

it is just not anything I would ever do. I do not typically use identity as a primary key but it just seems wrong to change the field that uniquely identifies a row.

====================================================
Regards,
Sean Roussy

"pimpin ain't easy, but someone has to do it" -- Pimpin Whitefolks(?)

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-09-08 : 21:36:01

>>but it just seems wrong to change the field that uniquely identifies a row.
I totally agree

Be One with the Optimizer
TG

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-08 : 21:53:50

will this work?

but be warned that this can cause some major blocks, did this before and the best solution was to just allow sql to handle how changes are cascaded...


create trigger trDepToEmp on departments
after update
as

update employees
set employees.departmentID=ValuesID.insertedID
from employees join (select inserted.id as 'insertedID',deleted.id as 'deletedID' from inserted join deleted
on inserted.name=deleted.name) valuesID
on employees.departmentID=valuesID.deletedID
where employees.departmentID=valuesID.deletedID

go

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

dve
Starting Member

19 Posts

Posted - 2005-09-09 : 01:59:19

Hi,

thanks for that trigger! It does the job good enough... Unfortunately it depends on the uniqueness of the 'name' column in 'departments' which isn't the case here...

but then again, I could just add a column to the departments table and fill it with unique values ;)

Too bad SQL server doesn't have the concept of ROWID like ORACLE

Thanks a million!

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2005-09-09 : 03:20:15

why is the name not unique? is it possible to have 2 Accounting departments for example?

just curious...

quote:
Originally posted by dve

Hi,

thanks for that trigger! It does the job good enough... Unfortunately it depends on the uniqueness of the 'name' column in 'departments' which isn't the case here...

but then again, I could just add a column to the departments table and fill it with unique values ;)

Too bad SQL server doesn't have the concept of ROWID like ORACLE

Thanks a million!

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

Kristen
Test

22859 Posts

Posted - 2005-09-09 : 03:47:19

"why is the name not unique"

Doesn't the column have to have the characteristic of not changing between "deleted" and "inserted"?

I'm thinking surrogate keys all over again

Kristen

dve
Starting Member

19 Posts

Posted - 2005-09-09 : 05:54:35

jen,

we're building a very specific application and i'm building these triggers dynamically to support this... I don't have a fixed schema, this schema changes as required by the end-users so I can't assume that any given column (like department name) for instance is even there, let alone is unique!

The good news is that I can use your trigger because we do have a unique column in each table ;o)

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources