Delete Trigger Problem - 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)

Delete Trigger Problem

Author

Topic

daipayan
Posting Yak Master

181 Posts

Posted - 2009-07-20 : 08:17:25

Hi all,

I have 2 table: batch and course_details
Both tables have two same columns: batchcode
I had declared a trigger:

CREATE TRIGGER deleteCourse ON batch
FOR DELETE
AS
delete
from course_details
where batch in (select batchCode from deleted)

This trigger will be fired when I have to delete batchcode from both the tables, but I am not inserted batchcode in course_details, but it is present in batch table, then I don't want want the trigger should be fired and batchcode can be deleted from batch table without giving any error message.
What change should I make in the trigger for this restriction?

Daipayan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 08:28:27

a delete trigger will always fire when you delete a row from that table. If the batch does not exists in course_details, there will be nothing to delete and you will not get an error.

KH
[spoiler]Time is always against us[/spoiler]

daipayan
Posting Yak Master

181 Posts

Posted - 2009-07-20 : 09:57:59

quote:
Originally posted by khtan

a delete trigger will always fire when you delete a row from that table. If the batch does not exists in course_details, there will be nothing to delete and you will not get an error.

KH
[spoiler]Time is always against us[/spoiler]

Am getting the following error while deleting the batchcode from Batch table:
Row cannot be located for updating. Some values may have been changed since it was last read.

Daipayan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 10:07:20

try to delete the row from batch table in Query Analyzer and see what's the error reported there

KH
[spoiler]Time is always against us[/spoiler]

daipayan
Posting Yak Master

181 Posts

Posted - 2009-07-20 : 10:19:33

quote:
Originally posted by khtan

try to delete the row from batch table in Query Analyzer and see what's the error reported there

KH
[spoiler]Time is always against us[/spoiler]

Sir,

Can't I use

IF EXISTS

code to give the restriction??

Daipayan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 10:26:17

it's not that. The trigger should not and will not return any error if there isn't any record to delete for course_details.

KH
[spoiler]Time is always against us[/spoiler]

daipayan
Posting Yak Master

181 Posts

Posted - 2009-07-20 : 10:30:48

Sir,
It's showing, I showed you the error message and if am removing the trigger, no error message is coming..

quote:
Originally posted by khtan

it's not that. The trigger should not and will not return any error if there isn't any record to delete for course_details.

KH
[spoiler]Time is always against us[/spoiler]

Daipayan

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-20 : 10:40:37

will you be able to provide the scripts (table DDL, data DML etc) that able to reproduce the problem ?

KH
[spoiler]Time is always against us[/spoiler]

daipayan
Posting Yak Master

181 Posts

Posted - 2009-07-20 : 13:41:30

Table: Batch

CREATE TABLE [dbo].[batch] (
	[batch_ID] [bigint] IDENTITY (1, 1) NOT NULL ,
	[batchCode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
) ON [PRIMARY]
GO

Table: Course_Details

CREATE TABLE [dbo].[course_details] (
	[course_code] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[course_name] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[course_specialisation] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[course_level] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[batch] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[faculty_firstname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[faculty_middlename] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[faculty_lastname] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[course_credit] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
	[course_website] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
	[course_outline] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Trigger:

CREATE TRIGGER deleteCourse ON batch
FOR DELETE
AS
delete
from course_details
where batch in (select batchCode from deleted)

THIS IS MY TABLE & TRIGGER!

quote:
Originally posted by khtan

will you be able to provide the scripts (table DDL, data DML etc) that able to reproduce the problem ?

KH
[spoiler]Time is always against us[/spoiler]

Daipayan

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources