SQL Trigger to create new record with V10

[Scholin]

I am converting my V9 web app to V10 along with moving to SQL.

I have a trigger that works just fine from SQL Management Server and works in V10 saving a new record AS LONG as the new record doesn't fire the trigger, then it won't save - it just sits there without any error messages but the save button won't fade.

Here is my trigger script which I feel comfortable is fine (from a SQL stand point):

ALTER TRIGGER [dbo].[Create_New_Ssep_Rec]
ON [dbo].[Patient]
After INSERT
AS
if exists(select * from inserted where SSEP='True')
insert dbo.SSEP_Result(Patient_ID)
select DISTINCT Patient_ID from inserted i
where not exists (select * from dbo.SSEP_Result where Patient_ID = i.Patient_ID)

It seems like V10 doesn't like triggers - is this the case or is there another approach?

Is there a way to create a new record using a SQL command in the "CanInsertRecord" Alpha Event if as in this case, it the field in the main patient table SSEP='True'?

Any SQL/V10 help would be appreciated.

Thanks!

[Andiyanto Bun]

Re: SQL Trigger to create new record with V10

i also use trigger with alpha v10 and it work well, but in my case i use mysql database.

regards,
Andy

[Scholin]

Re: SQL Trigger to create new record with V10

Andiyanto,

Thank you for the response. If I run a simple test page using V10 against this trigger, like I mentioned previously, V10 won't complete the save on screen, but when I look into the SQL table it did complete the insert, just as if I ran it directly from SQL - like it is suppose to.

Now, if I remove the condition on the trigger

if exists(select * from inserted where SSEP = 'True')

then V10 works fine and of course will create a new record every time, which I would rather it only creates a new record if the condition is true.

Any thoughts? Thanks.

[Bob Moore]

Re: SQL Trigger to create new record with V10

I'm not sure exactly what you are trying to do but if you are trying to have the trigger fire prior to submission of a new record then that's the problem. Remember .. a new record is not actually inserted into the table until it is submitted.

[Scholin]

Re: SQL Trigger to create new record with V10

Bob,

I think this may be similar the problem you were trying to solve at the conference.

Here is what I am trying to achieve over all:

I want to create a "conditional" one-to-one relationship between two tables. That is, when a new patient is created (or updated) and if a particular test is checked in the main patient table, say SSEP='true', then a new table will automatically be created to store the data and results for this one SSEP test - but there will never be more than one SSEP test for this one patient and not all patients will need this test, thus the need for a conditional one-to-one relationship. In the past I just created one huge dbf patient table - trying to avoid that wit V10 and SQL.

So in V10, although I could have it automatically create a related tables very nicely, the problem is that I don't want it to create more than one for this patient - so I have to created a new record first and tell V10 to not allow "inserts" on that table, only allow updates on an existing record(there must be a better way!).

Does this make sense?

[Bob Moore]

Re: SQL Trigger to create new record with V10

Michael,

You are way over-thinking this!

You don't want to create a related table, what you want is a child record in a related table.

Thus if a certain relationship is true, then you want to enter a child record. This can be easily handled by a conditional display of a linked grid.

Just keep in mind that you have to create the parent before you can add a child record. I do this by entering in a small portion of the parent record, say a record identifier like UUID (previously discussed) and patient name in a small grid. Once that is submitted, the parent record is created and I now bring up a new grid, filtered by the UUID, which brings up the newly created record with all of the information that you are trying to capture, including access to the child linked grid(s). Now based on a conditional display, you can allow the entry of a single child record.

You don't need to use any SQL Server side triggers to pull this off.

[Scholin]

Re: SQL Trigger to create new record with V10

Bob,

I agree 100% except for one part - how would you show the child on screen as a single record without showing the empty next record displaying below it?

That is, I have not been able to insert a child record without "allow insert" being checked on Update Settings which causes an empty record showing below it.

If I don't check "allow insert" (allowing update only), AND if a record is already created, then it will show up perfectly as a single record - which is my end goal.

Am I missing something here? I hope so . . please enlighten!

Thanks,

Mike

[Bob Moore]

Re: SQL Trigger to create new record with V10

Use a linked grid with a detail view.
The linked grid will be read-only.
Set the detail view to allow insert/update.

Now the only thing you need to figure out is how to allow only one child record to be entered. That's an area I haven't explored yet but I suspect you could use the CanInsertRecord event to handle this.

[eboughey1]

Re: SQL Trigger to create new record with V10

I have a similar issue, but mine is based on a view. This grid is for adding a new job to the system.

There is a dealer field that's a lookup field. It populates the dlrid, address info, dealer name on the form. It's supposed to add a new record to the child table if it doesn't exist. It only stores the dealer id in the parent table. Client wants aesthetics on the screen though showing all address info.

One dealer can be used for multiple jobs so can't use the uuid from the grid.

Where would I put the insert info? I had the following set up in a dialog, but I wouldn't be able to use the ajax functions with a dialog so am converting to grid

tbl = table.open("[PathAlias.ADB_Path]\dealername.dbf")

ix  = tbl.query_create("N","Dealername = "+quote(alltrim(CurrentForm.Controls.Dealername.value)))
recs = ix.records_get()

if recs = 0 then
        tbl.enter_begin()
 	     	tbl.dealername =  alltrim(CurrentForm.Controls.dealername.value)
 	 		tbl.address =  alltrim(CurrentForm.Controls.Address.value)
		 	tbl.Csz =  alltrim(CurrentForm.Controls.CSZ.value)
            tbl.phone =  alltrim(CurrentForm.Controls.phone.value)
            CurrentForm.Controls.dlrid = tbl.Dlrid
		tbl.enter_end()
            
	else 
	 	        CurrentForm.Controls.dlrid = tbl.Dlrid
				tbl.close()
end if

[Bob Moore]

Re: SQL Trigger to create new record with V10

So the relationship is one dealer has many jobs and one job has one dealer.
It may be easier to think of the dealer table as the parent and the jobs table as the child.
In this scenario, a linked grid from the dealer to the job is very easy to implement. The other way around gets tricky, unless you just want to use a table driven drop down box.

[eboughey1]

Re: SQL Trigger to create new record with V10

it is a little tricky. I was hoping to actually run the insert in the events like I would in a dialog instead.

In the dialog, it looks to see if dealername exists. If so, it updates the dlrid in the job table, otherwise it adds the new dealer into the dealer table and then updates the job table with the newly created dlrid.