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!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

sql database - triggers 1

Status
Not open for further replies.
dinivan

dinivan

Programmer
Dec 19, 2005
11
GB
Hi, I am new to sql databases and was wondering would anybody be able to help me with triggers.

I need to write a trigger which is fired when something is entered into the main table, it will then populate the sub table with the primary key from the main table.

Any help would greatly be appreciated.

cheers dinivan
 
Sort by date Sort by votes
Things to rememeber in using a trigger. Fisrt, you must make the trigger so it will work whether one or more records were inserted.

YOu have two psuedotables that are only available in triggers, inserted (which has the new data) and deleted (which has the old data). An insert only has data in inserted, a delete action only has data in deleted, and an update has information on both.

To insert data in a related table you will need to get the id field that is inserted for the data. That will be accomplished by using the pkfield from the inserted table and then adding what ever data you need for the other table to it as part of the insert.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief
 
Upvote 0 Downvote
Hi i have created this trigger but when i try to close it i get the following error

Error 213: Insert Error Column name or number of supplied values does not match table definition. Invalid column name 'Policy_ID'.

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

if @@rowcount = 0
return

Insert into tbl_policies_sub_donna
select Policy_ID from inserted
 
Upvote 0 Downvote
When you do an insert, yo ust supply values for allthe fields if you use the structure above. If you do not want to supply a vlaue for allfields then you need to define which fields youare supplying values for (you must include all required fields that do not have default values of course) in the following way:
Code: 
Insert into tbl_policies_sub_donna (Policy_Id, Field1)
select Policy_ID, 'text' from inserted

Questions about posting. See faq183-874
Click here to help with Hurricane Relief
 
Upvote 0 Downvote
I have ammended the trigger below as suggested but I am now gettign the following error

Error 207:Invalid column name 'Field1')

what does field on refer to.

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, Field1)
select PolicyID, 'int' from inserted

Any help is always appreciated

Cheers dinivan
 
Upvote 0 Downvote
Field1 was an example, you use the field names from your table that you need to insert a record for and then supply values for them.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief
 
Upvote 0 Downvote
Thanks SQLSister for all you help.

But I have one more problem I was hoping you would be able to help me with.

When the trigger 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.

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

<!-- #INCLUDE file="../incs/header.inc" -->
<!-- #INCLUDE file="../incs/dbconn_policies.inc" -->
<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>


<!-- #INCLUDE file="../incs/footer.inc" -->

 
Upvote 0 Downvote
Triggers always seem to do interesting and unexpected things for me. I think it's better to build the business logic into the query/stored procedure if at all possible.
Rather than relying on a trigger to populate your child tables for you, explicitely populate them yourself.
Follow your initial INSERT statement with a "select @@identity" (if you don't know what the key is beforehand) and use that to do subsequent INSERTs into the children tables.
 
Upvote 0 Downvote
jasen, never ever under any circumstances use @@identity! You will create data integrity problems if someone later adds triggers to your tables.

Triggers are good and necessary for keeping data integrity but not good for all jobs. However, you shouuld use them and constranits to ensure business rules are followed no matter how the data is entered or updated in your system. However, yes if you are entering dat on a form, it is best to get the identiy field back (using scope_identity) as part of the sp that inserts the data and then use that as a variable in the insert that inserts the data from the subform.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief
 
Upvote 0 Downvote
I don't really understand what you mean, could you please give me an example.

cheers dinivan
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

MathiasB
  • Question
Parameterised sql server query
Replies
8
Views
392
Chriss Miller
C
gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
952
pjw001
pjw001
Steve Yu
  • Locked
  • Question
How secure is MS SQL on the Internet vs behind firewall inside a LAN 1
Replies
3
Views
540
Chris Miller
Chris Miller
V
  • Locked
  • Question
xp_cmdshell vs psexec - this SQL server forgets credential to an W10 share
Replies
0
Views
761
Volvere
V
elly00
  • Locked
  • Question
create sql view using batch file
Replies
5
Views
637
Mike Lewis
Mike Lewis

Part and Inventory Search

Sponsor

Back
Top