December 22, 2008 at 5:43 am
I have a trigger that fires on a table everytime a record is updated to a status of 3.
I then gather all the data we need to insert into a report table.
I need to do the following:
If the formid = 6
Insert two separate records with slightly different data in two columns.
Else
Insert the data in the local variables
______________________________
AJ Mendo | @SQLAJ
December 22, 2008 at 5:48 am
Ok. Is there a question somewhere in that?
If you want assistance in writing the trigger, you're going to have to give a lot more details on what you want to do and what the tables look like.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2008 at 5:53 am
here's a sample trigger doing what you want....note it is assuming the update has more than one row occurring, so all the operations are set based on the virtual INSERTED table, which has the new values for the update.
I could only guess at what the tables you want to insert into where, but the model should get you started:
[font="Courier New"]CREATE TRIGGER [TR_YOURTABLE_STATUSCHANGED] ON YOURTABLE
FOR UPDATE
AS
BEGIN
--we don't want to prevent the insert, just do extra stuff if the
--status changes for the right formid
IF EXISTS(SELECT 1 FROM INSERTED WHERE STATUS=3 AND FORIMID=6)
BEGIN
INSERT INTO SomeStatusTable(STATUS,FORMID,DATECOMPLETED)
SELECT INSERTED.STATUS,INSERTED.FORMID,GETDATE() FROM INSERTED
WHERE STATUS=3 AND FORIMID=6
INSERT INTO ShippingQueue(INVOICEID,FORMID,DATEQUEUED)
SELECT INSERTED.INVOICEID,INSERTED.FORMID,GETDATE() FROM INSERTED
WHERE STATUS=3 AND FORIMID=6
END
END
[/font]
Lowell
December 22, 2008 at 6:10 am
Pseudo code
SET NoCount ON
If Exists(Select ... From Inserted where StatusID = 3 )
Begin
--Declare the variables
@FormID int...
Select
variables...
From Inserted
------------------------------------------------------------------------------
-- Settngs for other forms
Depending on the formid re-setting varibles to different values
IF @Formid = ...
SET variables...
------------------------------------------------------------------------------------------
-- Get the other info we need to insert based off of a variable in the insert/update data
-- setting variables based on a join
SELECT
@varables = variables...
FROM table
JOIN table...
------------------------------------------------------------------------------------------
-- Stored proc to insert the data needed for the report table
IF @Formid = 6
EXEC report_insert_data variables...
EXEC report_insert_data variables...
ELSE
EXEC report_insert_data variables...
______________________________
AJ Mendo | @SQLAJ
December 22, 2008 at 6:13 am
I am doing similar but the second insert is the only one inserting into the table.
______________________________
AJ Mendo | @SQLAJ
December 22, 2008 at 6:14 am
What's the question?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2008 at 6:16 am
Or use the new OUTPUT operator?
CREATE TRIGGER TR_YOURTABLE_STATUSCHANGED
ON YOURTABLE
AFTER UPDATE
AS
INSERT SomeStatusTable
(
STATUS,
FORMID,
DATECOMPLETED
)
OUTPUT i.INVOICEID,
i.FORMID,
GETDATE()
INTO ShippingQueue
(
INVOICEID,
FORMID,
DATEQUEUED
)
SELECT i.STATUS,
i.FORMID,
GETDATE()
FROM INSERTED AS i
WHERE i.STATUS = 3
AND i.FORIMID = 6
N 56°04'39.16"
E 12°55'05.25"
December 22, 2008 at 6:43 am
Not sure how this helps me do separate inserts....
Peso (12/22/2008)
Or use the new OUTPUT operator?
CREATE TRIGGER TR_YOURTABLE_STATUSCHANGED
ON YOURTABLE
AFTER UPDATE
AS
INSERT SomeStatusTable
(
STATUS,
FORMID,
DATECOMPLETED
)
OUTPUT i.INVOICEID,
i.FORMID,
GETDATE()
INTO ShippingQueue
(
INVOICEID,
FORMID,
DATEQUEUED
)
SELECT i.STATUS,
i.FORMID,
GETDATE()
FROM INSERTED AS i
WHERE i.STATUS = 3
AND i.FORIMID = 6
______________________________
AJ Mendo | @SQLAJ
December 22, 2008 at 7:00 am
Read and understand the code.
And as a sidenote, why would you like to store some data in local variables?
You do know an UPDATE can affect more than one record?
N 56°04'39.16"
E 12°55'05.25"
December 22, 2008 at 7:21 am
your issue is you are locked into thinking about one row at a time:
you think the trigger will affect one and only one row, and that you can grab a handfull of variables and stick them into this proc call:
EXEC report_insert_data variables...
in a trigger, you cannot assume one row. never. Its bad practice and poor design.
my rule of thumb is, if i see a variable declaration in a trigger, the trigger is wrong, period. I don't even need to read the code, because I KNOW the design is thinking row-by-agonizing row instead of as a set.
SELECT STUFF FROM INSERTED WHERE STATUS = 3 is a set of data NOT ONE ROW...the INSERTED table may contain a dozen rows......but your trigger needs to affect maybe 3 of those dozen rows for specific business logic...
so any operation that is occurring must assume it will affect ALL the rows in the set. you do not need an If formid = 6 else...[all other formid] do something like the modification below.
get rid of the call the procs form the trigger idea.
[font="Courier New"]
CREATE TRIGGER [TR_YOURTABLE_STATUSCHANGED] ON YOURTABLE
FOR UPDATE
AS
BEGIN
--we don't want to prevent the insert, just do extra stuff if the
--status changes for the right formid
IF EXISTS(SELECT 1 FROM INSERTED WHERE STATUS=3)
BEGIN
INSERT INTO SomeReportTable(STATUS,FORMID,DATECOMPLETED)
SELECT INSERTED.STATUS,INSERTED.FORMID,GETDATE() FROM INSERTED
WHERE STATUS=3 AND FORIMID=6
INSERT INTO SomeOtherReportTable(INVOICEID,FORMID,DATEQUEUED)
SELECT INSERTED.INVOICEID,INSERTED.FORMID,GETDATE() FROM INSERTED
WHERE STATUS=3 AND FORIMID <> 6
END
END[/font]
Lowell
December 22, 2008 at 7:47 am
actually, thinking about it some more, the table could be updated, but the status was already 3...
for exmaple, if a NOTES field was updated int eh table, but the status was already 3, you'd probably not want to re-insert into the report, right...
so you need to explicitly check that the INSERTEd.Value is not the same as the previous DELETED.Value:
[font="Courier New"] IF EXISTS(SELECT 1
FROM INSERTED
INNER JOIN DELETED
ON INSERTED.PKOFTHETABLE= DELETED.PKOFTHETABLE
--only if this field changed THIS time
WHERE INSERTED.STATUS <> DELETED.STATUS
AND INSERTED.STATUS = 3)[/font]
Lowell
December 22, 2008 at 7:58 am
Doesn't
IF UPDATE(colStatus)
do that for you?
N 56°04'39.16"
E 12°55'05.25"
December 22, 2008 at 8:25 am
yes, but he needs the same WHERE clauses in his subsequent inserts/updates to his other three tables....so I thought he needed the comparison for the later SQL's....
same thing, different island, i guess.
Lowell
December 22, 2008 at 8:28 am
Peso (12/22/2008)
Doesn'tIF UPDATE(colStatus)
do that for you?
That just checks if the column was referenced in the update statement. If the column is updated to itself, UPDATED will be true for that column.
Create Table TestTrigger (id int)
go
CREATE TRIGGER TestingTriggers
ON TestTrigger
AFTER UPDATE
AS
IF UPDATE (id)
RAISERROR ('Column Updated', 16, 10)
GO
insert into TestTrigger values (1)
go
update TestTrigger set id = id
Results of update:
Msg 50000, Level 16, State 10, Procedure TestingTriggers, Line 6
Column Updated
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 22, 2008 at 8:34 am
I see some of your points but not all.
The reason I am declaring and setting variables is because not all the data for the reports is in the first table. I have to join on other tables to get the data, that's why I am doing that. As you say a bad design, I will investigate more to determine a better way. Thanks
______________________________
AJ Mendo | @SQLAJ
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply