Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 190
  • Last Modified:

sql trigger after update not working in one section

I am having problems with this portion:

--allow a valid NMSR Date to be inserted
if (@srdate is not null) and (@srcompletedate is null) and (@nmsrdate_delete is null) and (@nmsrdate is not null) and (@nmsrdate >= @srdate and @nmsrdate <= @currentdate)
      begin
            
            update [Service Database] set [NMSR_Date] = @nmsrdate_delete where [Service Report No] = @srnum
    end

When I try to change the current NULL value for the NMSR_Date column for that record to any date, the value return back to NULL when it should have taken the new value for the date.

Please help.  Thanks.

Sanjay
script.txt
NullNMSRDateTrigger-UPDATE-ServiceDataba
0
sxxgupta
Asked:
sxxgupta
1 Solution
 
DcpKingCommented:
Look at your condition again. It only runs if @nmsrdate_delete is null (line 3 of the listing below). And, if that is so, it sets NMSR_Date to @nmsrdate_delete (which is what it was before the trigger was activated!)

hth

Mike



	if 	(@srdate is not null) 
		and (@srcompletedate is null) 
		and (@nmsrdate_delete is null) 
		and (@nmsrdate is not null) 
		and (@nmsrdate >= @srdate and @nmsrdate <= @currentdate)
	begin
		--raiserror (50009,11,1) with nowait
		update [Service Database] 
			set [NMSR_Date] = @nmsrdate_delete 
			where [Service Report No] = @srnum
    end

Open in new window

0
 
sxxguptaAuthor Commented:
Yup.  I noticed that.  Thanks.
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now