inserting problem with trigger 1

dinivan · Dec 28, 2005

I hope someone will be able to help me with the problem I am having.

When the trigger i have attached to my main table is fired it is entering the policyID from the main table into the sub table but it is entering the information entered through the form into a separate row in the sub table instead of the same row as the primary key was entered into.

Any help with this would greatly be appreciated.

Below is my trigger and asp code.

Trigger

CREATE TRIGGER PolicyID ON [dbo].[tbl_policy_main_donna]
FOR INSERT, UPDATE, DELETE
AS

if @@rowcount = 0
return

Insert into tbl_policies_sub_donna (PolicyID)
select PolicyID 'int' from inserted

ASP Code



<html>
<head>
<SCRIPT LANGUAGE="Javascript">

function ValidateTextboxes() {
if (document.addPolicy.policyref.value.length < 1 || document.addPolicy.fileref.value.length < 1 || document.addPolicy.linkeddoc.value.length < 1 || document.addPolicy.source.value.length < 1 || document.addPolicy.leadperson.value.length < 1 || document.addPolicy.writtendate.value.length < 1 || document.addPolicy.revieweddate.value.length < 1){
alert("You must fill in all the fields.");
return false;
}
else{
return true;}

}
</SCRIPT></head>
<body>
<%
Dim Conn
'Create an ADO connection and recordset object
Set Conn = Server.CreateObject("ADODB.Connection")
Set Rs= Server.CreateObject("ADODB.Recordset")
'Set an active connection and select fields from the database

Conn.Open "Driver={SQL Server};Server=nbhq-sql01;Database=policies;Uid=scott;Pwd=password;"

strQuery = "SELECT * FROM tbl_policy_main_donna;"
strQuery2 = "SELECT * FROM tbl_policies_sub_donna;"

Set rstPolicy = Conn.Execute(strQuery)
Set rstPolicy2 = Conn.Execute(strQuery2)

Dim policyRef, fileRef, linkedDoc, policySource, leadPerson, writtenDate, reviewedDate

policyRef = Request.Form("policyref")
fileRef = Request.Form("fileref")
linkedDoc = Request.Form("linkeddoc")
policySource = Request.Form("source")
leadPerson = Request.Form("leadperson")
writtenDate = Request.Form("writtendate")
reviewedDate = Request.Form("revieweddate")

Sub insertData(policyRef, fileRef, linkedDoc, policySource, leadPerson, writtenDate, reviewedDate)

If ((policyRef = "") or (fileRef = "") or (linkedDoc = "")) Then

Else
Set rstInsertPolicyMain = Conn.Execute("INSERT INTO tbl_policy_main_donna(Policy_Ref, File_Reference, linked_document) VALUES('" & policyRef & "','" & fileRef & "','" & linkedDoc & "' );")
'rstPolicy1.Close
'rstPolicy1 = nothing
Set rstInsertPolicySub = Conn.Execute("INSERT INTO tbl_policies_sub_donna(Source, Lead_Person, Date_Written, Date_Revised) VALUES('" & policySource & "','" & leadPerson & "','" & written & "','" & reviewed & "' );")
'rstPolicy1.Close

End If
End Sub
%>
<form name="addPolicy" method="post" action="<% call insertData(policyRef, fileRef, linkedDoc, policySource, leadPerson, writtenDate, reviewedDate) %>" onSubmit="return ValidateTextboxes();">
<table width="75%" border="0" align="center" bordercolor="#000066">
<tr>
<td bgcolor="#FFFFFF">

<div align="center">
<p><strong>Enter the new Policy below and Click on Submit</strong></p>
<table width="37%" border="1" bordercolor="#000066">
<tr>
<td width="52%" bgcolor="#99CCFF"><font color="#FFFFFF">Policy Ref</font></td>
<td width="48%"><input type="text" name="policyref" maxlength="45"></td>
</tr>

<tr>
<td bgcolor="#99CCFF"><font color="#FFFFFF">Source</font></td>
<td><input type="text" name="source" maxlength="45"></td>
</tr>
<tr>
<td bgcolor="#99CCFF"><font color="#FFFFFF">Lead Person</font></td>
<td><input type="text" name="leadperson" maxlength="45"></td>
</tr>
<tr>
<td bgcolor="#99CCFF"><font color="#FFFFFF">Written</font></td>
<td><input type="text" name="writtendate" maxlength="45"></td>
</tr>
<tr>
<td bgcolor="#99CCFF"><font color="#FFFFFF">Reviewed</font></td>
<td><input type="text" name="revieweddate" maxlength="45"></td>
</tr>
<tr>
<td bgcolor="#99CCFF"><font color="#FFFFFF">File Reference</font></td>
<td><input type="text" name="fileref" maxlength="45"></td>
</tr>
<tr>
<td bgcolor="#99CCFF"><font color="#FFFFFF">Linked Document</font></td>
<td width="48%"><input type="text" name="linkeddoc" maxlength="45"></td>
</tr>
</table>
<p>
<input type="submit" name="Save" value="Submit">

</p>
</div>
<div align="center">
<p><a href="javascript:self.close()">Close this window</a> </p>
</div></td>
</tr>
</table>
</form>
</body>
</html>

ChrisCarroll · Dec 28, 2005

I would guess that the reason your trigger is inserting rows into the subtable is because you have the line

Code:

[b]insert[/b] into tbl_policies_sub_donna ....

So you've told it to insert.

Your mistake seems to be to declare your trigger for update and delete as well as insert. Why not declare it as:

Code:

CREATE TRIGGER PolicyID ON [dbo].[tbl_policy_main_donna]
FOR INSERT --but not for update!
AS

dinivan · Dec 28, 2005

Hi Chris,
I suppose I should explain from the start what i'm trying to do.

I need to take the policyID from the main table and insert it into the policyID field in the sub table. I have achieved this by creating a trigger which is executed once info has been inserted into the main table (through a form).

But my problem is that the policyID is not inserted into the same row in the sub table as the information which has been entered through the same form.

Hope this makes things a little bit clearer.

maswien · Dec 28, 2005

dinivan,

The trigger will insert a row to the sub table with the policy_id field populated and other fields left with blank, so the trigger works fine. The problem is in your ASP code, you have two insert statement, the first fires the trigger and a new row gets inserted to the sub table, then the second insert statement insert another row directly to the sub table and this is not what you want. To fix that, you need get rid of the trigger and implement all this in a single stored procedure, then call this stored procedure in ASP code,

stored procedure:

Code:

CREATE TRIGGER PolicyID ON [dbo].[tbl_policy_main_donna] 
FOR INSERT, UPDATE, DELETE 
AS

if @@rowcount = 0
return

create procedure add_policy
@policyRef ..., 
@fileRef ..., 
@linkedDoc ..., 
@policySource ..., 
@leadPerson ..., 
@writtenDate ..., 
@reviewedDate ...

begin
Insert into tbl_policies_sub_donna (PolicyID)
select PolicyID  'int' from inserted

INSERT INTO tbl_policy_main_donna
(Policy_Ref, File_Reference, linked_document) 
VALUES( @policyRef, @fileRef, @linkedDoc )

-- I guess your policy_id is a identity field
declare @cur_id as int
select @cur_id = IDENT_CURRENT('tbl_policy_main_donna') 
              
INSERT INTO tbl_policies_sub_donna(policy_id, Source,  
                Lead_Person, Date_Written, Date_Revised) 
VALUES(@cur_id, @policySource, @leadPerson, @writtendate,   
       @reviewed)

end

maswien · Dec 28, 2005

sorry for the mess, get rid of the trigger, use stored procedure:

Code:

create procedure add_policy
@policyRef ..., 
@fileRef ..., 
@linkedDoc ..., 
@policySource ..., 
@leadPerson ..., 
@writtenDate ..., 
@reviewedDate ...

begin
Insert into tbl_policies_sub_donna (PolicyID)
select PolicyID  'int' from inserted

INSERT INTO tbl_policy_main_donna
(Policy_Ref, File_Reference, linked_document) 
VALUES( @policyRef, @fileRef, @linkedDoc )

-- I guess your policy_id is a identity field
declare @cur_id as int
select @cur_id = IDENT_CURRENT('tbl_policy_main_donna') 
              
INSERT INTO tbl_policies_sub_donna(policy_id, Source,  
                Lead_Person, Date_Written, Date_Revised) 
VALUES(@cur_id, @policySource, @leadPerson, @writtendate,   
       @reviewed)

end

maswien · Dec 28, 2005

still mess, should be:

Code:

create procedure add_policy
@policyRef ..., 
@fileRef ..., 
@linkedDoc ..., 
@policySource ..., 
@leadPerson ..., 
@writtenDate ..., 
@reviewedDate ...

begin

INSERT INTO tbl_policy_main_donna
(Policy_Ref, File_Reference, linked_document) 
VALUES( @policyRef, @fileRef, @linkedDoc )

-- I guess your policy_id is a identity field
declare @cur_id as int
select @cur_id = IDENT_CURRENT('tbl_policy_main_donna') 
              
INSERT INTO tbl_policies_sub_donna(policy_id, Source,  
                Lead_Person, Date_Written, Date_Revised) 
VALUES(@cur_id, @policySource, @leadPerson, @writtendate,   
       @reviewed)

end

dinivan · Dec 28, 2005

Thanks for the advice Maswien,
i'll give it a go at work tomorrow.

Cheers dinivan.

dinivan · Dec 29, 2005

Hi maswien,
i was wondering if you would be able to explain to me how to execute the procedure using asp.

I have done a search on the web but am still not sure, any help would be gratefully appreciated.

cheers dinivan

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

inserting problem with trigger 1

dinivan

Programmer

ChrisCarroll

Programmer

dinivan

Programmer

maswien

Technical User

maswien

Technical User

maswien

Technical User

dinivan

Programmer

dinivan

Programmer

Similar threads

Part and Inventory Search

Sponsor