Prev: Update stored procedure question
Next: Check Constraint and function call
From: DavidC on 16 Jun 2010 15:15
I have the delete trigger below and want to run an sp inside of it. I keep
getting an error trying to refer to a column in the table being deleted. I
am using deleted.RepCompany for the value in the sp and it is giving me a
syntax error. How can I populate the parameter for @OldData as shown below.
Thanks

ALTER TRIGGER [dbo].[T_RepCompany_DTrig] ON [dbo].[RepCompany]
FOR DELETE
AS
SET NOCOUNT ON

DECLARE @usrnm varchar(15);
SET @usrnm = RIGHT(SYSTEM_USER,LEN(SYSTEM_USER) -
CHARINDEX('\',SYSTEM_USER));

/* * CASCADE DELETES TO 'RepCompanyBrandsLink' */
DELETE RepCompanyBrandsLink FROM deleted, RepCompanyBrandsLink WHERE
deleted.RepCompanyID = RepCompanyBrandsLink.RepCompanyID
/* * CASCADE DELETES TO 'RepCompanyIndustryLink' */
DELETE RepCompanyIndustryLink FROM deleted, RepCompanyIndustryLink WHERE
deleted.RepCompanyID = RepCompanyIndustryLink.RepCompanyID
/* * CASCADE DELETES TO 'RepContactLinks' */
DELETE RepContactLinks FROM deleted, RepContactLinks WHERE
deleted.RepCompanyID = RepContactLinks.RepCompanyID
/* * CASCADE DELETES TO 'TerritoryLinks' */
DELETE TerritoryLinks FROM deleted, TerritoryLinks WHERE
deleted.RepCompanyID = TerritoryLinks.RepCompanyID

EXECUTE [dbo].[fd_insChangeTracking]
@TableName = 'RepCompany'
,@AddChgDel = 'D'
,@OldData = deleted.RepCompany
,@LoginID = @usrnm;

--
David
From: Tom Moreau on 16 Jun 2010 16:28
What error message do you get?

--
Tom

----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau


"DavidC" <dlchase(a)lifetimeinc.com> wrote in message
news:68A265A5-720E-4EED-AE76-0DF4F50AD7AD(a)microsoft.com...
I have the delete trigger below and want to run an sp inside of it. I keep
getting an error trying to refer to a column in the table being deleted. I
am using deleted.RepCompany for the value in the sp and it is giving me a
syntax error. How can I populate the parameter for @OldData as shown below.
Thanks

ALTER TRIGGER [dbo].[T_RepCompany_DTrig] ON [dbo].[RepCompany]
FOR DELETE
AS
SET NOCOUNT ON

DECLARE @usrnm varchar(15);
SET @usrnm = RIGHT(SYSTEM_USER,LEN(SYSTEM_USER) -
CHARINDEX('\',SYSTEM_USER));

/* * CASCADE DELETES TO 'RepCompanyBrandsLink' */
DELETE RepCompanyBrandsLink FROM deleted, RepCompanyBrandsLink WHERE
deleted.RepCompanyID = RepCompanyBrandsLink.RepCompanyID
/* * CASCADE DELETES TO 'RepCompanyIndustryLink' */
DELETE RepCompanyIndustryLink FROM deleted, RepCompanyIndustryLink WHERE
deleted.RepCompanyID = RepCompanyIndustryLink.RepCompanyID
/* * CASCADE DELETES TO 'RepContactLinks' */
DELETE RepContactLinks FROM deleted, RepContactLinks WHERE
deleted.RepCompanyID = RepContactLinks.RepCompanyID
/* * CASCADE DELETES TO 'TerritoryLinks' */
DELETE TerritoryLinks FROM deleted, TerritoryLinks WHERE
deleted.RepCompanyID = TerritoryLinks.RepCompanyID

EXECUTE [dbo].[fd_insChangeTracking]
@TableName = 'RepCompany'
,@AddChgDel = 'D'
,@OldData = deleted.RepCompany
,@LoginID = @usrnm;

--
David

From: Eric Isaacs on 16 Jun 2010 16:35
David,

Delete triggers do not run for each row deleted, they run for each
delete statement executed on the table. If you delete * from a table,
the trigger will be called once. Your sproc call is only designed to
handle one row. You should be doing what the sproc is doing in a set
operation rather than on a row by row process.

As far as the cascade delete statements, I would strongly suggest you
do those as constraints, rather than triggers with delete statements.
What you're proposing would work, but the constraints are a more
typical way of doing that and you would know that they will do the
job, when they're implemented, whereas doing it in code could
introduce bugs.

Sample Cascade Delete constraint:

ALTER TABLE Books
ADD CONSTRAINT fk_author
FOREIGN KEY (AuthorID)
REFERENCES Authors (AuthorID) ON DELETE CASCADE

-Eric Isaacs
From: Erland Sommarskog on 16 Jun 2010 17:59
DavidC (dlchase(a)lifetimeinc.com) writes:
> I have the delete trigger below and want to run an sp inside of it. I
> keep getting an error trying to refer to a column in the table being
> deleted. I am using deleted.RepCompany for the value in the sp and it
> is giving me a syntax error.
>...
> EXECUTE [dbo].[fd_insChangeTracking]
> @TableName = 'RepCompany'
> ,@AddChgDel = 'D'
> ,@OldData = deleted.RepCompany
> ,@LoginID = @usrnm;


You get a syntax error, because in a call to a stored procedure
you can only pass constants and variables. "deleted.RepCompany"
is an expression, why it is not legal.

Beside that, since it is a column reference, it can only appear in
a query. So for instance this is also illegal:

SET @myvar = deleted.RepCompany

As your actual question, the best my be to do the work of the
procedure within the trigger as you then can handle all deleted
rows at once. Alternatively, you could make a set-based version
of the procedure.

The quick solution to use the current procedure, is to run a trigger
over deleted.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

 | 
Pages: 1
Prev: Update stored procedure question
Next: Check Constraint and function call