SQL Server : How to know the table state if it is INSERTED or UPDATED

Hi

I am creating a stored procedure in SQL SQL Server 2005 and I want to create a CURSOR that can be chaged depending on a table state ... so if a table state is INSERTED I will create Cursor1 but if the table state is UPDATED then I will create Cursor2

Both Cursor1 and Cursor2 have different structure ......

Any help for this

Thanks
venmarcesAsked:
Who is Participating?
 
Scott PletcherSenior DBACommented:
Yeah, you can get rid of the cursor completely.  It will hurt your performance a LOT.

Something like below should do it.  I tried to be as accurate as possible, but it may still need tweaked.  It should give you a good idea of a non-cursor approach to take.
ALTER TRIGGER [dbo].[QuoteworkflowUpdate]
   ON  [dbo].[QuoteWorkflow] 
   AFTER INSERT, UPDATE
AS 
SET NOCOUNT ON;
DECLARE @action char(6);
IF EXISTS(SELECT TOP 1 * FROM deleted)
    SET @action = 'UPDATE'
ELSE
    SET @action = 'INSERT';
UPDATE qwf
SET
    LastFollowUpDate = CASE 
        WHEN qwf.BODDateSentCostumer >= qwf.QuoteDateSentCostumer AND qwf.BODDateSentCostumer >= qwf.RevisionDateSentCostumer AND
            qwf.BODDateSentCostumer >= qwf.DateCreationQuote  THEN  qwf.BODDateSentCostumer
        WHEN qwf.QuoteDateSentCostumer >= qwf.BODDateSentCostumer AND qwf.QuoteDateSentCostumer >= qwf.RevisionDateSentCostumer AND
            qwf.QuoteDateSentCostumer >= qwf.DateCreationQuote  THEN  qwf.QuoteDateSentCostumer
        WHEN qwf.RevisionDateSentCostumer >= qwf.BODDateSentCostumer AND qwf.RevisionDateSentCostumer >= qwf.QuoteDateSentCostumer AND 
            qwf.RevisionDateSentCostumer >= qwf.DateCreationQuote  THEN  qwf.RevisionDateSentCostumer
        ELSE
            qwf.DateCreationQuote END,
    BODCostumerRequestedDate = CASE WHEN qwf.BODCostumerRequestedDate IS NULL AND (qwf.BasisOfDesign = 0) AND (qwf.QuoteType IS NULL) AND
        qwf.RevisionType IS NULL THEN qwf.DateCreationQuote + 5 ELSE qwf.BODCostumerRequestedDate END,
    QuoteDateRFQ = CASE WHEN qwf.QuoteDateRFQ IS NULL THEN qwf.DateCreationQuote ELSE qwf.QuoteDateRFQ END,
    QuoteCostumerRequestedDate = CASE WHEN qwf.QuoteType IS NOT NULL AND qwf.QuoteCostumerRequestedDate IS NULL THEN
        CASE qwf.QuoteType
            WHEN 'Budget Price' THEN qwf.DateCreationQuote + 1 
            WHEN 'Quotation' THEN qwf.DateCreationQuote + 2
            ELSE qwf.QuoteCostumerRequestedDate END
        ELSE qwf.QuoteCostumerRequestedDate END,
    RevisionCostumerRequestedDate = CASE WHEN qwf.RevisionType IS NOT NULL AND qwf.RevisionCostumerRequestedDate IS NULL THEN
        CASE qwf.RevisionType
            WHEN 'Quote' THEN qwf.DateCreationQuote + 1
            WHEN 'Basis Of Design' THEN qwf.DateCreationQuote + 2 
            ELSE qwf.RevisionCostumerRequestedDate
        ELSE qwf.RevisionCostumerRequestedDate END
FROM dbo.QuoteWorkFlow qwf
INNER JOIN inserted i ON
    qwf.QuoteID = i.QuoteID;

Open in new window

0
 
EyalCommented:
in UPDATE mode the deleted has no rows so you can check it to define the state
0
 
baretreeCommented:
you cannot check the inserted/deleted tables within a stored procedure, but you can do that in a trigger and then call your SP with a parameter. You cannot use cursors inside a trigger as well.

so you can :

create trigger yourTriggerName on yourTableName for insert, update
as
/* the code */
     if (select count(*) from deleted > 0)
          execute yourStoredProcedureName 'U'
      else
          execute yourStoredProcedureName 'I'
...



0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Scott PletcherSenior DBACommented:
>> You cannot use cursors inside a trigger as well. <<

You can use cursors inside a trigger.  Not a good idea if it can be avoided, but they can be used.
0
 
venmarcesAuthor Commented:
Thanks Gents for your responses, but to be sure .... here is a code I want to put in my Trigger and I want just to be sure that I am doing in the right way

The cursor will already provide one row, do I need really a cursor or the best should be to put SELECT XXXXX INTO Variables and that sit.

see my code and let me know about your inputs

thanks  

0
 
venmarcesAuthor Commented:
ALTER TRIGGER [dbo].[QuoteworkflowUpdate]
   ON  [dbo].[QuoteWorkflow]
   AFTER INSERT, UPDATE
AS
BEGIN

      SET NOCOUNT ON;

      DECLARE @MostRecentDate DATETIME, @BODDateSentCostumer DATETIME, @QuoteDateSentCostumer DATETIME, @QuoteDateRFQ DATETIME, @RevisionCostumerRequestedDate DATETIME
    DECLARE @RevisionDateSentCostumer DATETIME, @DateCreationQuote DATETIME, @BODCostumerRequestedDate DATETIME, @QuoteCostumerRequestedDate DATETIME
      DECLARE @BasisOfDesign BIT
      DECLARE @QuoteType NVARCHAR(50), @RevisionType NVARCHAR(50)
      DECLARE @QuoteID INT
      SET @MostRecentDate = ''

            DECLARE C_Quote CURSOR FOR

  IF select count (*) from deleted > 0  

            
            SELECT QuoteID, BODDateSentCostumer,QuoteDateSentCostumer, RevisionDateSentCostumer, DateCreationQuote, BODCustomerRequestedDate, BasisOfDesign, QuoteType, RevisionType, QuoteDateRFQ, QuoteCostumerRequestedDate, RevisionCostumerRequestedDate  
            FROM QuoteWorkFlow, INSERTED, UPDATED  
            WHERE QuoteID = UPDATED.QuoteID

  ELSE


            SELECT QuoteID, BODDateSentCostumer,QuoteDateSentCostumer, RevisionDateSentCostumer, DateCreationQuote, BODCustomerRequestedDate, BasisOfDesign, QuoteType, RevisionType, QuoteDateRFQ, QuoteCostumerRequestedDate, RevisionCostumerRequestedDate  
            FROM QuoteWorkFlow, INSERTED, UPDATED  
            WHERE QuoteID = INSERTED.QuoteID             OPEN C_Quote
            FETCH NEXT FROM C_Quotes INTO @QuoteID, @BODDateSentCostumer, @QuoteDateSentCostumer, @RevisionDateSentCostumer, @DateCreationQuote, @BODCostumerRequestedDate, @BasisOfDesign, @QuoteType, @RevisionType, @QuoteDateRFQ, @QuoteCostumerRequestedDate, @RevisionCostumerRequestedDate
0
 
Scott PletcherSenior DBACommented:
>> do I need really a cursor or the best should be to put SELECT XXXXX INTO Variables <<

Preferably neither one.  Best is to use the inserted or deleted table and process it using standard SQL.  What type of processing do you need to do after that?

If you really must use a cursor, you can use the same for either one.  You don't need to join back to the original table for any rows affected by the trigger (for UPDATEd rows, you have both the before and after row images available to you in the inserted and deleted tables).



Btw, you should use IF EXISTS() rather than COUNT(*) for efficiency:

ALTER TRIGGER ...
...
AS
DECLARE @action char(6)
IF EXISTS(SELECT TOP 1 * FROM deleted)
    SET @action = 'UPDATE'
ELSE
    SET @action = 'INSERT'
...
0
 
venmarcesAuthor Commented:
Here is the code I want to build around my Trigger. I believe that it is not so optimized, but if you have better idea then I will appreciate

Thanks again  
ALTER TRIGGER [dbo].[QuoteworkflowUpdate]
   ON  [dbo].[QuoteWorkflow] 
   AFTER INSERT, UPDATE
AS 
BEGIN

	SET NOCOUNT ON;

	DECLARE @MostRecentDate DATETIME, @BODDateSentCostumer DATETIME, @QuoteDateSentCostumer DATETIME, @QuoteDateRFQ DATETIME, @RevisionCostumerRequestedDate DATETIME
    DECLARE @RevisionDateSentCostumer DATETIME, @DateCreationQuote DATETIME, @BODCostumerRequestedDate DATETIME, @QuoteCostumerRequestedDate DATETIME
	DECLARE @BasisOfDesign BIT
	DECLARE @QuoteType NVARCHAR(50), @RevisionType NVARCHAR(50)
	DECLARE @QuoteID INT 
	SET @MostRecentDate = ''

		DECLARE C_Quote CURSOR FOR 
		
		SELECT QuoteID, BODDateSentCostumer,QuoteDateSentCostumer, RevisionDateSentCostumer, DateCreationQuote, BODCustomerRequestedDate, BasisOfDesign, QuoteType, RevisionType, QuoteDateRFQ, QuoteCostumerRequestedDate, RevisionCostumerRequestedDate   
		FROM QuoteWorkFlow, INSERTED, UPDATED   
		WHERE QuoteID = INSERTED.QuoteID OR QuoteID = UPDATED.QuoteID 

		OPEN C_Quote 
		FETCH NEXT FROM C_Quotes INTO @QuoteID, @BODDateSentCostumer, @QuoteDateSentCostumer, @RevisionDateSentCostumer, @DateCreationQuote, @BODCostumerRequestedDate, @BasisOfDesign, @QuoteType, @RevisionType, @QuoteDateRFQ, @QuoteCostumerRequestedDate, @RevisionCostumerRequestedDate 

			IF @BODDateSentCostumer>@MostRecentDate 
			BEGIN 
				SET @MostRecentDate=@BODDateSentCostumer 
			END
	 
			IF @QuoteDateSentCostumer>@MostRecentDate 
			BEGIN
				SET @MostRecentDate=@QuoteDateSentCostumer 
			END 

			IF @RevisionDateSentCostumer>@MostRecentDate 
			BEGIN 
				SET @MostRecentDate=@RevisionDateSentCostumer 
			END 

			IF @DateCreationQuote>@MostRecentDate 
			BEGIN 
				SET @MostRecentDate=@DateCreationQuote 
			END 

			IF (@BODCostumerRequestedDate = Null) AND (@BasisOfDesign = 0) AND (@QuoteType = Null) AND (@RevisionType = Null)
			BEGIN 
				SET @BODCostumerRequestedDate = @DateCreationQuote + 5  
			END 

			IF Not (@QuoteType = Null) AND (@QuoteCostumerRequestedDate = Null) 
			BEGIN 
				SET @QuoteCostumerRequestedDate = CASE @QuoteType WHEN 'Budget Price' THEN @DateCreationQuote + 1 WHEN 'Quotation' THEN @DateCreationQuote + 2 END 
			END 

			IF Not (@RevisionType = Null) AND (@RevisionCostumerRequestedDate = Null) 
			BEGIN 
				SET @RevisionCostumerRequestedDate = CASE @RevisionType WHEN 'Quote' THEN @DateCreationQuote + 1 WHEN 'Basis Of Design' THEN @DateCreationQuote + 2 END 
			END 


			IF (@QuoteDateRFQ = Null) 
			BEGIN 
				SET @QuoteDateRFQ = @DateCreationQuote 
			END 

		CLOSE C_Quote

	UPDATE QuoteWorkFlow 
	SET LastFollowUpDate = @MostRecentDate, 
		BODCostumerRequestedDate = @BODCostumerRequestedDate,  
		QuoteDateRFQ = @QuoteDateRFQ , 
		QuoteCostumerRequestedDate = @QuoteCostumerRequestedDate, 
 		RevisionCostumerRequestedDate = @RevisionCostumerRequestedDate
	WHERE QuoteID = @QuoteID 

END

Open in new window

0
 
venmarcesAuthor Commented:
Well , sounds good for ... So from what I see no cursor is required and thats great

However, you @Action is not used i.e: you Updated QWF by making a join table on INSERTED i but you never checked before if the QWF statement is UPDATED or INSERTED and depnding on which @Action you are ... then you will make a join on the appropriate statement INSERTED or UPDATED

How you can do this
0
 
Scott PletcherSenior DBACommented:
True, but I'm not sure it matters to your processing whether it's an INSERT or an UPDATE, does it?

Do you do make different changes to the column if it's an INSERT vs an UPDATE?

If so, add an IF and write a separate UPDATE for:

IF @action = 'INSERT'
BEGIN
    UPDATE ...
ELSE
    UPDATE ...


Either way, always avoid cursors if you can, because they are HUGE overhead.
0
 
venmarcesAuthor Commented:
Well it doesn't matter for me if it is INSERTED or UPDATED the only thing that I am concerned is about the last JOIN

FROM dbo.QuoteWorkFlow qwf
INNER JOIN inserted i ON
    qwf.QuoteID = i.QuoteID;


OR

FROM dbo.QuoteWorkFlow qwf
INNER JOIN UPDATED u ON
    qwf.QuoteID = u.QuoteID;

The code before is applied for both statements .... Is there anyway to gather both JOIN in the same line such like :  

FROM dbo.QuoteWorkFlow qwf
INNER JOIN inserted i, UPDATED u ON
    qwf.QuoteID = i.QuoteID OR qwf.QuoteID = u.QuoteID;

Thanks



0
 
Scott PletcherSenior DBACommented:
You can, but you don't need to.

The inserted and deleted (not updated) tables will contain the same QuoteIDs.

The inserted table has the rows after they were updated, the deleted table has the (same) rows before they were updated.

In this case, the only need for the INNER JOIN to the inserted table is to restrict the UPDATE statement to only the rows directly affected by the INSERT/UPDATE statement that caused the trigger to fire.
0
 
venmarcesAuthor Commented:
Thanks again for your help
0
 
venmarcesAuthor Commented:
Good answer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.