Solved

MS SQL Server - New Record Condition

Posted on 2014-11-19
30
134 Views
Last Modified: 2016-02-13
Hi Experts,

I have this bit of code:
If New RECORD
Begin
update CAQH_MAIN
set
STAT_CD = 'COMPLETED',
STAT_TS = Getdate(),
CAQH_USR_KY = NULL,
VALDTN_STAT = 'D',
VALDTN_RSN = 'NONG',
VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING') OR Where GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)
Else
update CAQH_MAIN
set
STAT_CD = 'COMPLETED',
STAT_TS = Getdate(),
VALDTN_STAT = 'D',
VALDTN_RSN = 'NONG',
VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING')
END

Open in new window


But as you can see the If New Record is not correct?

Please help thanks.
0
Comment
Question by:Amour22015
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 13
  • 10
  • 5
  • +1
30 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 40452811
>But as you can see the If New Record is not correct?
Please copy-paste any errors that are returned into this question, and define for us what 'is not correct' means.
Mind readers we ain't.
0
 

Author Comment

by:Amour22015
ID: 40452824
Well I have not executed this because I am sure that the "If New Record" is not a correct syntax?  But maybe I am wrong?
0
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 50 total points
ID: 40452827
For starters...
IF requires a boolean expression that evaluates to true or false.  Not sure if 'IF New Record' is your notes, or if this has a meaning somewhere..
You are missing an END and BEGIN around the ELSE line.
Indenting your code would make it much easier to read.

If New RECORD  - define this as a boolean expression that evaluates to true or false
   Begin
   
   update CAQH_MAIN
   set
      STAT_CD = 'COMPLETED',
      STAT_TS = Getdate(),
      CAQH_USR_KY = NULL,
      VALDTN_STAT = 'D',
      VALDTN_RSN = 'NONG',
      VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
   WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING') OR Where GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)
   
   end  -- here
Else
   begin -- and here
   
   update CAQH_MAIN
   set
      STAT_CD = 'COMPLETED',
      STAT_TS = Getdate(),
      VALDTN_STAT = 'D',
      VALDTN_RSN = 'NONG',
      VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
   WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING')

   END

Open in new window

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40452831
If you want some updates to occur automatically when a new row is added, we need to add an "AFTER INSERT" trigger on your table.

We can use an "AFTER UPDATE" trigger to handle rows that are updated later, after the initial insert.

What are the key column(s) on the CAQH_MAIN table?  The trigger has to join from the inserted table to the CAQH_MAIN table to make sure that only newly inserted/updated rows are affected by the trigger(s).
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40452835
WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING') OR Where GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)

>>>

WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING') OR GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)
0
 

Author Comment

by:Amour22015
ID: 40453344
Ok,

First let me say that I am very new to all this.

But from what I so far think I understand it would be something like:

GO
CREATE TRIGGER CAQH_Trigger - Do I even use this in another area?
    AFTER INSERT ON CAQH_MAIN
    FOR EACH ROW BEGIN
   
   update CAQH_MAIN
   set
      STAT_CD = 'COMPLETED',
      STAT_TS = Getdate(),
      CAQH_USR_KY = NULL,
      VALDTN_STAT = 'D',
      VALDTN_RSN = 'NONG',
      VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
   WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING') OR GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)
   
   END
Else
   BEGIN
   
   update CAQH_MAIN
   set
      STAT_CD = 'COMPLETED',
      STAT_TS = Getdate(),
      VALDTN_STAT = 'D',
      VALDTN_RSN = 'NONG',
      VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
   WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING')

   END

Open in new window



So is this correct?

Please help and thanks
0
 
LVL 53

Assisted Solution

by:Huseyin KAHRAMAN
Huseyin KAHRAMAN earned 150 total points
ID: 40453450
what are you trying to do? are you trying to create a trigger?  

maybe this helps...

CREATE TRIGGER CAQH_Trigger -- Do I even use this in another area?
    ON CAQH_MAIN
	AFTER INSERT  
as
begin

   update CAQH_MAIN
   set
      STAT_CD = 'COMPLETED',
      STAT_TS = Getdate(),
      CAQH_USR_KY = NULL,
      VALDTN_STAT = 'D',
      VALDTN_RSN = 'NONG',
      VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
   WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING') OR GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)
     AND some_id in (select some_id from inserted)
   
   update CAQH_MAIN
   set
      STAT_CD = 'COMPLETED',
      STAT_TS = Getdate(),
      VALDTN_STAT = 'D',
      VALDTN_RSN = 'NONG',
      VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
   WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING')
     AND some_id in (select some_id from inserted)

END;

Open in new window

0
 

Author Comment

by:Amour22015
ID: 40453486
I don't know if I need a trigger or not.

It was mentioned to my understanding, in order for SQL to know that there is a "New Record" I guess since the last time a SSIS package was ran. One would need a trigger "Insert" had happened to the table?

What I am trying to do:

If a "New Record" was added to the table then:
update CAQH_MAIN
   set
      STAT_CD = 'COMPLETED',
      STAT_TS = Getdate(),
      CAQH_USR_KY = NULL,
      VALDTN_STAT = 'D',
      VALDTN_RSN = 'NONG',
      VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
   WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING') OR GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)

Open in new window


Else:
begin 
   
   update CAQH_MAIN
   set
      STAT_CD = 'COMPLETED',
      STAT_TS = Getdate(),
      VALDTN_STAT = 'D',
      VALDTN_RSN = 'NONG',
      VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
   WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING')

End

Open in new window


Thanks for any help
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40453495
"If a "New Record" was added to the table then:" >>> which table?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40453557
As I stated earlier:

Yes, for new rows, you need a trigger OR set the column defaults to the values you want.

For updates, you'll need a trigger.

To code either trigger correctly, we'll need to know what the key col(s) on the table are so that the table can be joined to the inserted table.
0
 

Author Comment

by:Amour22015
ID: 40453603
Sorry if I seem a bit slow, still new to all this.

This is the table "CAQH_MAIN" when there is a "New Record" added then:
update CAQH_MAIN
   set
      STAT_CD = 'COMPLETED',
      STAT_TS = Getdate(),
      CAQH_USR_KY = NULL,
      VALDTN_STAT = 'D',
      VALDTN_RSN = 'NONG',
      VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
   WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING') OR GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)

Open in new window




So I guess the:
Inserted table would be "CAQH_Trigger":
GO
CREATE TRIGGER CAQH_Trigger - Do I even use this in another area?
    AFTER INSERT ON CAQH_MAIN
    FOR EACH ROW BEGIN

Open in new window


How would I work the Join?, also when this is first executed (using SSIS 2008) then this part would only run once:
update CAQH_MAIN
   set
      STAT_CD = 'COMPLETED',
      STAT_TS = Getdate(),
      VALDTN_STAT = 'D',
      VALDTN_RSN = 'NONG',
      VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
   WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING')

Open in new window

Otherwise it would run all the time which would be a repeat?

I guess I don't understand.

Thanks for helping me.
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40453645
maybe something like this... two trigger one for insert one for update...

CREATE TRIGGER CAQH_Trigger_Insert ON CAQH_MAIN AFTER INSERT  AS
BEGIN
  UPDATE CAQH_MAIN
  set
      STAT_CD = 'COMPLETED',
      STAT_TS = Getdate(),
      CAQH_USR_KY = NULL,
      VALDTN_STAT = 'D',
      VALDTN_RSN = 'NONG',
      VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
  FROM CAQH_MAIN m inner join inserted i on c.some_id=i.some_id
  WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING') OR GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122);           
END;

CREATE TRIGGER CAQH_Trigger_Update ON CAQH_MAIN AFTER UPDATE AS
BEGIN
  UPDATE CAQH_MAIN
  set
      STAT_CD = 'COMPLETED',
      STAT_TS = Getdate(),
      VALDTN_STAT = 'D',
      VALDTN_RSN = 'NONG',
      VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
  FROM CAQH_MAIN m inner join inserted i on c.some_id=i.some_id
  WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING');
END;

Open in new window

0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40453667
Yes, that's self-evident, and the trigger coding itself is trivial for us.

But, I'll state yet again, it can't be done accurately without knowing the name of the key column(s) for that table.


Amour22015:
Please run this command in an SSMS Query window while connected to that db:

EXEC sp_helpindex CAQH_MAIN

Look for an index that is "unique", particularly "clustered, unique".  Look at the column names in that line and let us know what they are.
0
 

Author Comment

by:Amour22015
ID: 40453672
Ok,

That looks good, but where are you getting the i ?  I know what you mean: i.some_id  but where is the i coming from?

Is that the inserted i ?  If yes then the inserted is a key word?

I see this:
FROM CAQH_MAIN m inner join inserted i on c.some_id=i.some_id

But don't you mean:
FROM CAQH_MAIN m inner join inserted i
on m.some_id = i.some_id

Please help and thanks
0
 

Author Comment

by:Amour22015
ID: 40453687
This is what I get:
index_name               index_description                                                             index_keys
PK_CAQH_MAIN      clustered, unique, primary key located on PRIMARY      CAQH_ky
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 300 total points
ID: 40453744
CREATE TRIGGER CAQH_MAIN__TRG_INS
ON CAQH_MAIN
AFTER INSERT
AS
SET NOCOUNT ON;
update cm
set
STAT_CD = 'COMPLETED',
STAT_TS = Getdate(),
CAQH_USR_KY = NULL,
VALDTN_STAT = 'D',
VALDTN_RSN = 'NONG',
VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
FROM CAQH_MAIN cm
INNER JOIN inserted i ON
    i.CAQH_ky = cm.CAQH_ky
WHERE
    i.STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING') OR
    i.GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40453748
CREATE TRIGGER CAQH_MAIN__TRG_UPD
ON CAQH_MAIN
AFTER UPDATE
AS
SET NOCOUNT ON;
update cm
set
STAT_CD = 'COMPLETED',
STAT_TS = Getdate(),
VALDTN_STAT = 'D',
VALDTN_RSN = 'NONG',
VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
INNER JOIN inserted i ON
     i.CAQH_ky = cm.CAQH_ky
WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING')
GO
0
 
LVL 53

Expert Comment

by:Huseyin KAHRAMAN
ID: 40453826
"i" is an alias for "inserted" table, there, in the query ^^^... and "some_id" is the pk of your table, which is "CAQH_ky" that you should know :)

there are two tables involved in triggers, inserted & deleted
one holds new records and the other one hold old/deleted values

when inserting, deleted will be null
when deleting inserted will be null
when updating both will have something...

and yes, they are reserved names for those special tables involved in triggers...
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40453965
CORRECTION: I left out the "FROM" clause in my second trigger:

CREATE TRIGGER CAQH_MAIN__TRG_UPD
 ON CAQH_MAIN
 AFTER UPDATE
 AS
 SET NOCOUNT ON;
 update cm
 set
 STAT_CD = 'COMPLETED',
 STAT_TS = Getdate(),
 VALDTN_STAT = 'D',
 VALDTN_RSN = 'NONG',
 VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
FROM CAQH_MAIN cm
 INNER JOIN inserted i ON
      i.CAQH_ky = cm.CAQH_ky
 WHERE STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING')
 GO
0
 

Author Comment

by:Amour22015
ID: 40455019
Ok,

For some reason I am getting this on the Update:
Msg 8197, Level 16, State 4, Procedure CAQH_MAIN__TRG_UPD, Line 1
The object 'CAQH_MAIN' does not exist or is invalid for this operation.

And this on the insert:
Msg 8197, Level 16, State 4, Procedure CAQH_MAIN__TRG_INS, Line 1
The object 'CAQH_MAIN' does not exist or is invalid for this operation.

Please help and thanks
0
 

Author Comment

by:Amour22015
ID: 40455056
Ok,

I have this so far:
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'CAQH_MAIN__TRG_INS' AND type = 'TR')
   DROP TRIGGER CAQH_MAIN__TRG_INS
GO
CREATE TRIGGER CAQH_MAIN__TRG_INS
 ON CAQH_MAIN
 AFTER INSERT
 AS
 SET NOCOUNT ON;
 update cm
 set
 STAT_CD = 'COMPLETED',
 STAT_TS = Getdate(),
 CAQH_USR_KY = NULL,
 VALDTN_STAT = 'D',
 VALDTN_RSN = 'NONG',
 VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
 FROM CAQH_MAIN cm
 INNER JOIN inserted i ON
     i.CAQH_ky = cm.CAQH_ky 
 WHERE 
     i.STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING') OR 
     i.GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122) 
    

Open in new window


But I am still getting:
Msg 8197, Level 16, State 4, Procedure CAQH_MAIN__TRG_INS, Line 1
 The object 'CAQH_MAIN' does not exist or is invalid for this operation.

Please help and thanks
0
 

Author Comment

by:Amour22015
ID: 40455064
Don't know why but it no longer has the error:
Msg 8197, Level 16, State 4, Procedure CAQH_MAIN__TRG_INS, Line 1
  The object 'CAQH_MAIN' does not exist or is invalid for this operation.

???

that it did before?
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40455692
Is CAQH_MAIN a table or is it a view?

Also, add the schema just to be sure you're pointing to the correct object:

CREATE TRIGGER CAQH_MAIN__TRG_INS
ON dbo.CAQH_MAIN
...
0
 

Author Comment

by:Amour22015
ID: 40455741
CAQH_MAIN  = Table

But it stopped with the error (don't know why) after I put in the:
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'CAQH_MAIN__TRG_INS' AND type = 'TR')
   DROP TRIGGER CAQH_MAIN__TRG_INS
GO

Open in new window

Section.

How do I test this?
So I am creating a trigger table? Where do I find this table in SQL?  Are trigger's automatically deleted after the query has been run?

So does "After Insert" mean that the record(s) have been added since the last time the Trigger was run?

Please help and thanks
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40455940
For the 'doesn't exist' error, I think you're probably just in the wrong db.  Make sure you're not in the master db, as that is the most likely default db.
0
 

Author Comment

by:Amour22015
ID: 40455949
'doesn't exist' error = already taken care of.

How do I test this query and find what took place after running?
 So I am creating a trigger table? Where do I find this table in SQL?  Are trigger's automatically deleted after the query has been run?

 So does "After Insert" mean that the record(s) have been added since the last time the Trigger was run?

 Please help and thanks
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40455956
Triggers are on existing tables, so, no, there's not a "trigger table".

Triggers are not automatically dropped/deleted.  They continue to run for every INSERT or UPDATE after being created until you explicitly drop them yourself.

"AFTER INSERT" means that, after the trigger is created, every INSERT into the table -- in this case, CAQH_MAIN -- the trigger code will run after that insert.  But note that a trigger in SQL Server runs only once per INSERT statement/action, even if many rows are INSERTed at the same time.
0
 

Author Comment

by:Amour22015
ID: 40455972
So let me try to be clear on your last statement.

You are saying that:
Trigger's attach themselves to the table and every time there is a Insert/Update this same query:
USE COB_DISCOVERY
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'CAQH_MAIN__TRG_INS' AND type = 'TR')
   DROP TRIGGER CAQH_MAIN__TRG_INS
GO
CREATE TRIGGER CAQH_MAIN__TRG_INS
 ON CAQH_MAIN
 AFTER INSERT
 AS
 SET NOCOUNT ON;
 update cm
 set
 STAT_CD = 'COMPLETED',
 STAT_TS = Getdate(),
 CAQH_USR_KY = NULL,
 VALDTN_STAT = 'D',
 VALDTN_RSN = 'NONG',
 VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
 FROM CAQH_MAIN cm
 INNER JOIN inserted i ON
     i.CAQH_ky = cm.CAQH_ky 
 WHERE 
     i.STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING') OR 
     i.GRP_NR IN (470014, 470025, 470038, 470050, 470062, 470075, 470086, 470098, 470110, 470122) 
     

Open in new window


is going to be Executed?

If so then this query:
USE COB_DISCOVERY
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'CAQH_MAIN__TRG_UPD' AND type = 'TR')
   DROP TRIGGER CAQH_MAIN__TRG_UPD
GO
CREATE TRIGGER CAQH_MAIN__TRG_UPD
  ON CAQH_MAIN
  AFTER UPDATE
  AS
  SET NOCOUNT ON;
  update cm
  set
  STAT_CD = 'COMPLETED',
  STAT_TS = Getdate(),
  VALDTN_STAT = 'D',
  VALDTN_RSN = 'NONG',
  VALDTN_ADDL_OI_NM = 'IND INDEMNITY'
FROM CAQH_MAIN cm
  INNER JOIN inserted i ON
       i.CAQH_ky = cm.CAQH_ky 
  WHERE i.STAT_CD IN ('AVAILABLE', 'PENDING', 'WORKING')
  GO

Open in new window


should not be a trigger because it is only going to be run once because it is only for existing records?

Please help so I can finish this posting, thanks
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 40456013
Correct.  If you want to run something only once, it should not be a trigger, just run the code itself.

The INSERT trigger, as noted above, will run after every INSERT on the table from now on, but it will affect only the row(s) that were INSERTed by the statement that just ran.
0
 

Author Closing Comment

by:Amour22015
ID: 40456141
Great thanks for all your help.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question