A couple of things concern me Leif in your example before I get to your
answer. Since you have posted pseudo code, I will try to do my best.
One of things about triggers is the Inserted and Updated tables in the
trigger are in fact tables and they can contain multiple records within
them. As long as you are conscious of this you're ok. Right now the way
you have written this code only one record from lets say 2 or more inserted
records would be written into the procedure.
Also, it is not good form to set a variable with the SELECT statement. This
is proprietary to SQL Server it is much easier to understand and portable to
use the SET keyword to set variables.
Finally, its ok to put code into triggers. You should only look to put code
in a procedure outside of trigger if you feel as though you may use it in
other places within your application or its highly complex and would benefit
being outside the trigger. Otherwise, quick and concise code within a
trigger is ok.
With that out of the way let's discuss how to catch a specific event in
T-SQL
I will assume that data1a in Table 2 is unique or primary key for the table.
CREATE PROCEDURE usp_TestDaat
AS
BEGIN
END
With the IF statement in front of the Insert I make the determination as to
whether or not the variable is located within the table. If its not then I
am going to insert the record, if the value does exist I won't do anything
at all. This simple piece of code will ensure that a primary key violation
never occurs in this procedure. This is proactive programming and I use it
all the time in my position as a SQL developer. When I can catch something
like this, I generally set an output parameter to a number other then zero
and I give my developers definitions of each error value returned in the
output parameter.
My own error catch for transactional procedures goes like this:
BEGIN
ROLLBACK TRANSACTION
RETURN
END
Let's say you wanted to flag the error as its coming out of the procedure
you create the procedure like the following:
CREATE PROCEDURE usp_TestDaat
AS
BEGIN
END
ELSE
BEGIN
RETURN
END
already within the table.
If I were to write this same code you posted, I would do it like what's
found bottom of this post. My code has the added benefit of being able to
deal with multiple record inserts. Again, this is just pseudo code you
posted so I can only write against what you posted. However, perhaps it
will help you understand what I am trying to say. Hope all of this helps
you and sorry for being a little long winded. Proactive error catching
along with XML are two of my big topics in SQL Server.
Grant
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[TI_Table1]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[TI_Table1]
GO
CREATE TRIGGER TI_Table1
ON dbo.Table1
FOR INSERT
AS
/*********************************HDR*************************************
*********GENERAL*********
OBJECT NAME: TI_Table1
OBJECT TYPE: Trigger
SUBCATEGORY: Data Integrity
CREATED BY: GRANT CASE
DATE: 01/18/2004
MODIFIED BY: GRANT CASE
DATE: 01/18/2004
DESCRIPTION:
This trigger writes information from the table1 table on insert into
the Table2 table if it is not already located there.
*********VARIABLES*********
NAME TYPE LOCAL/PASSED DEFAULT
DESCRIPTION
---------------------------------------------------------------
*********DEPENDENCIES*********
TABLES:
OBJECTS:
*********TEMP TABLES*********
NAME DESCRIPTION/USE
*********TRIGGER SPECIFIC*********
SOURCE TABLE:
DESTINATION TABLE:
CASCADED TRG:
CASCADED TRG TABLE:
*********REPORT SPECIFIC*********
REPORTS USING STORED PROCEDURE:
*********OUTPUT*********
*********MODIFICATION LOG*********
DATE INITIALS MODIFICATION
01/18/2004 GSC Created
*********HEADER CONVENTIONS*********
DO NOT GO PAST 75 CHARACTERS BEFORE GOING TO THE NEXT LINE
TO DEBUG, REPLACE ALL "-- DEBUG"
*********************************HDR*************************************/
--------------------------------------------------------------------------
-- LOCAL VARIABLE DECLARATION SECTION
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- LOCAL VARIABLE SET SECTION
--------------------------------------------------------------------------
--IF YOU DO NOT SET THIS, ACTIVE SERVER PAGES WILL HAVE PROBLEMS
SET NOCOUNT ON
WHEN (SELECT COUNT(*) FROM DELETED) = 0 AND (SELECT COUNT(*)
FROM INSERTED) = 0 THEN 'None'
WHEN (SELECT COUNT(*) FROM DELETED) > 0 AND (SELECT COUNT(*)
FROM INSERTED) > 0 THEN 'Update'
WHEN (SELECT COUNT(*) FROM DELETED) = 0 AND (SELECT COUNT(*)
FROM INSERTED) > 0 THEN 'Insert'
WHEN (SELECT COUNT(*) FROM DELETED) > 0 AND (SELECT COUNT(*)
FROM INSERTED) = 0 THEN 'Delete'
END
--------------------------------------------------------------------------
-- TEMP TABLE CREATION SECTION
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- CODE SECTION
--------------------------------------------------------------------------
INSERT INTO Table2
SELECT Data1a
FROM Inserted
LEFT OUTER JOIN Table2 ON Data1a = Data1
WHERE Data1a IS NULL
--------------------------------------------------------------------------
-- TESTING SECTION
--------------------------------------------------------------------------
--------------------------------------------------------------------------
-- CLEAN UP SECTION
--------------------------------------------------------------------------
GO
by