Problem with deleted data from ON DELETE trigger

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

SQL Server Development (2000)

Problem with deleted data from ON DELETE trigger

Author

Topic

horwitzlaw
Starting Member

1 Post

Posted - 2005-06-16 : 10:43:11

Hi,

Is it possible to access other table with the information provided from the deleted data from a trigger?

I have this below I am trying to get from our calendar table, but it get the error...

The column prefix 'calendar' does not match table or alias used in query

AND

The column prefix 'deleted' does not match table or alias used in query

SET @BUserial = (select calendar.serial from calendar,deleted where deleted.cal_id = calendar.serial)
SET @BUcase_id = (select case_id from calendar,deleted where deleted.cal_id = calendar.serial)
SET @BUtype = (select type from calendar,deleted where deleted.cal_id = calendar.serial)
SET @BUwhat = (select what calendar,deleted where deleted.cal_id = calendar.serial)
SET @BUstatus = (select statuscalendar,deleted where deleted.cal_id = calendar.serial)
/*SET @BUmemo = (select cast(memo as char) from deleted)*/
SET @BUabfld = (select calendar,deleted where deleted.cal_id = calendar.serial)
SEt @Pdate = getdate() --(select postdate from deleted)
/*SET @BUtickler = (select tickler.name from tickler, calendar where calendar.serial = @BUserial)*/

Thanks in advance
eric

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-06-16 : 13:32:24

You at least need to correct the syntax of this line of code. I suspect that you meant to include a FROM clause.


select calendar,deleted where deleted.cal_id = calendar.serial

CODO ERGO SUM

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-06-16 : 13:35:17

You have a fundamental problem with your trigger code, using variables. You can not assume that only one row will exist in the deleted or inserted table. Please see this weblog for more information:
http://weblogs.sqlteam.com/tarad/archive/2004/09/14/2077.aspx

And yes you can access other tables from within a trigger. But keep in mind that adding too much logic to a trigger is not a good thing. Keep trigger code small and efficient so that the transaction can complete as fast as possible.

Tara

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources