November 16, 2007 at 2:45 am
Hello comunity
I would to know in a trigger (Update or Insert or Delete) i can use an IF clause or CASE.
Could someone give me a simple example.
Many thanks
Luis Santos
November 16, 2007 at 2:59 am
Do you have something like this in mind?
CREATE TABLE mytable
( a INT IDENTITY
PRIMARY KEY
NOT NULL
, b INT
)
go
CREATE TRIGGER mytrigger ON mytable
FOR INSERT
AS
BEGIN
IF ( SELECT COUNT(*)
FROM inserted
) != 1
BEGIN
RAISERROR ( 'You are inserting more than one row', 16, 1 )
END
DECLARE @insertedB INT
SELECT @insertedB = b
FROM inserted ;
IF @insertedB = 1
PRINT 'inserted 1'
ELSE
IF @insertedB = 2
PRINT 'inserted 2'
ELSE
PRINT 'inserted 3'
END
Regards,
Andras
November 16, 2007 at 4:46 am
Many thanks for your reply.
In fact i have an application Client/server , the Client is build in VFP and BD is SQLSRVER.
i pretend for example when my invoice document have code=1 when i fired the trigger to do an Update,Delete or Insert, the Trigger use my invoice code to do the correct action on an another table for example or the same one the update a table field
Also, in my example the invoicecode=1 , how to declare this variable on my Trigger and replace them with my onvoice code
Many thanks
Luis Santos
November 16, 2007 at 7:57 am
A trigger has many of the same rules as stored procedures. The main exception is you can't execute a trigger; it fires based on the parameters of the trigger (for instance, INSERT against a table if it's been defined in that manner). Also, triggers have access to certain temporary tables, deleted and inserted, that are only valid within the context of triggers. Otherwise, the majority of constructs available in a stored procedure are available in a trigger. There are a few exceptions, but not many.
K. Brian Kelley
@kbriankelley
November 16, 2007 at 11:16 am
Hello Brian
Ok, i agree with you, but you did not answer to my question and i have the same doubt.
Andras give me an example , my unique question is to place a variable on my trigger.
Have you a suggestion to my question or anyone
Many thanks
Luis Santos
November 16, 2007 at 12:28 pm
A trigger fires on a SQL operation. Therefore, a trigger could be handling multiple rows, not just one. So you must take that into account.
When a trigger fires, two special tables are available: inserted and deleted. Here is how they work depending on the operation:
INSERT:
inserted - contains the new row(s) being inserted into the table
deleted - not used
UPDATE:
inserted - contains the rows as they will appear after the UPDATE operation
deleted - contains the rows as they were before the UPDATE operation
DELETE:
inserted - not used
deleted - contains the row(s) being deleted
It sounds like you need to query the inserted table and cycle through the rows one-by-one using a CURSOR. Then as you process each row, you'll need to use IF to branch based on the value of the column.
Books Online covers both the inserted and deleted trigger as well as how to define and use a cursor.
K. Brian Kelley
@kbriankelley
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply