Trigger for Inserting
-
Hi, I need a trigger that will insert a value into a column of another table, when a valued inserted in the orginal table.
I have 2 tables: branches and Class, these 2 tables have a relationship through their columns
(branches table)............(class table)
BranchID..................BranchID
-------------..................--------
......1.......>>>trigger>>>>......1....
I hope this diagram makes it simpler to understand. When I enter '1' in 1st column(branches) a trigger should fire and insert the same value in the 2nd column(class). I've tried this code but it dosnt work:
CREATE TRIGGER branch_branchID_class_branchID
ON branches
FOR INSERT
AS
INSERT c set
branchID = (c.branchID = i.branchID)
FROM class as c INNER JOIN Inserted as i
ON c.branchID = i.branchID
This parses OK, but when I check the class table, no value has been inserted. Guys i would appreciate any help. I've only been doing sql for 3 weeks, so please keep ur explainations quite simple
Thanks
-
To insert a record into the class table when a record is inserted into the brances table.
CREATE TRIGGER branch_branchID_class_branchID
ON branches
FOR INSERT
AS
INSERT into Class (BranchID)
Select BranchID
from Inserted
This means it will insert a new record into class for every new row inserted into Branch.
Lite reading about triggers inserted, Deleted tables.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_08_0lo3.asp
-
Thanks, Your a super star. I can see where my code was wrong. Thanks again you saved me a lot of time
-
attn: Enthusiast
Your solution works fine, however when I try insert in other columns from the class table, I get an error message saying:
Cannot insert the value NULL into column 'branchID'
but this column is not null as it has a value from the trigger you gave me in ur earlier post....
the class table consists of 4 colums:
classID - which the primary key and identifier
ClassNo - populated from a user defined function
branchID - populated by the insert trigger.
capacity - populated by a user defined function.
my insertion function for classNo @ capacity is
ALTER PROC [insert class]
@classID int, @classNo int, @capacity int
AS
BEGIN
INSERT class
(classID, classNo, capacity)
VALUES(@classID, @classNo, @capacity)
END
I also set the identity insert to off for this table.
I execute the insert function by;
EXEC [insert class]
@classNo = 2,
@capacity = 20,
@classID = 1
finally when I execute I get an error msg stating that a column branchID does not accept nulls, this is because i have exluded it from the insert function, as it gets its values from the trigger.
can u see where im going wrong?
-
EXEC [insert class]
@classNo = 2,
@capacity = 20,
@classID = 1
finally when I execute I get an error msg stating that a column branchID does not accept nulls, this is because i have exluded it from the insert function, as it gets its values from the trigger.
The trigger you created is an Insert trigger on the branches table. It will not be fired by an insert into the class table.
If you will need to create a class record before the matching branches record is created, you will have to allow nulls in the branchid column of the classes table. However, this is probably not in compliance with your business rule.
Just to clarify your original question, you want to create a record in the class table each time a new record is created in the branches table?
Do you also want to create a new branch record each time you create a new record in the class table?
If you want the process to work both ways, you will need to be careful to avoid an endless loop of triggers firing other triggers which in turn fire the first trigger. To avoid this you will need a test in the triggers to search for the new branchid in the other table before inserting a record.
-
I just reread your previous post and I think I see your problem.
Your solution works fine, however when I try insert in other columns from the class table, I get an error message saying:
Cannot insert the value NULL into column 'branchID'
but this column is not null as it has a value from the trigger you gave me in ur earlier post....
When you add the other columns' values in the class table, you will need to use the UPDATE command rather than the INSERT. Insert creates a new record. You want to update an existing record with data in the other columns.
UPDATE class
SET classID = @classID, classNo = @classNo, capacity = @capacity
WHERE branchID = @branchID
You will need to know which record you are updating (the branchID) and I am assuming the branchID is unique in the class table. If it is not unique and you can have multiple classes for each branchID, the WHERE clause of the INSERT command would need to be expanded.
One possibility is to change the WHERE clause to;
WHERE branchID = @branchID AND classID IS NULL
Again with the assumptions, namely that you will never have more than one record in the class table with the same branchID and no classID assigned.
Could you explain the non-technical process for creating the data?
Would the user creating the new branchID always know the classID to be assigned?
Does the user adding the class information know the branchID? or is this all part of one transaction?
Sorry for the long list of questions, but without knowing the process and the restrictions on that process any advice given will be generic. I think you are asking for something more specific to your situation.
HTH
-
Thanks for your contribution grasshopper.
Let me explain a few things about my database.
the database is going to be used as a scheduling sysyem for a collge that has branches in many different cities across the UK.
The 2 tables of concern and branches & class.
branches(branchID, address, tel, postcode, city)
with branchID being a unique nonclustered index and identifier on the branches table, and unique non clustered index on the class table, but not an identifier
class(classId, classNo, branchID, capacity)
Since these to tables have 2 columns in common, I want to just insert a value into branches(branchID) once, and then that value should be automatically inserted into the class(branchID) column.
no values can be added to the class table without details being inserted into the branches table first.
Ideally, i should only have enter manually values into the class table for the columns: classID, classNo and capacity.
This is where the problem starts as my insert function does not allow null values in the branchID column of class, but the column is not null as it is valuated by the trigger on the branches table.
to answer you earliler questions:
Again with the assumptions, namely that you will never have more than one record in the class table with the same branchID and no classID assigned. -- Yes
Would the user creating the new branchID always know the classID to be assigned? -- Yes
Does the user adding the class information know the branchID? -- Yes
-
Mark,
Would you post the DDL of the tables? It would help to see the actual definitions.
The easist way to get the DDL is to right-click the table name in Enterprise Manger, select All Tasks, Generate SQL Scripts, click the Preview Button, click the Copy button, then paste the result into your reply. (for each table)
By 'identifier' do you mean the field has the Identity property set? If so, the trigger on the Branches table will create a ClassID when it fires. If you need to assign the ClassID rather than let SQL Server assign it, the procedure will be a bit more complicated.
One situation not addressed is when you want to add a second (or third, fourth, etc) class to a branch. This could not be done via a trigger on the Branch table. When setting up the classes in the class table, you would need to enter the BranchID.
What you have described is a classic Parent-Child relationship between the two tables. You will probably want to create a Foreign Key constraint on the Class table if you have not already done so.
Do you forsee this database being replicated in the future?
-
attn: Grasshopper, here are the scripts for the 2 tables.
Branches:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_schedule_branches]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[schedule] DROP CONSTRAINT FK_schedule_branches
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[branch_branchID_class_branchID]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[branch_branchID_class_branchID]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[branches]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[branches]
GO
CREATE TABLE [dbo].[branches] (
[branchID] [int] IDENTITY (1, 1) NOT NULL ,
[address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[postcode] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[city] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[branches] WITH NOCHECK ADD
CONSTRAINT [PK_branches] PRIMARY KEY CLUSTERED
(
[branchID]
 
ON [PRIMARY]GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER branch_branchID_class_branchID
ON branches
FOR INSERT
AS
INSERT INTO class(branchID)
SELECT branchID
FROM Inserted
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Class:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_schedule_class]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[schedule] DROP CONSTRAINT FK_schedule_class
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[class]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[class]
GO
CREATE TABLE [dbo].[class] (
[classID] [int] IDENTITY (1, 1) NOT NULL ,
[classNo] [int] NULL ,
[branchID] [int] NOT NULL ,
[capacity] [tinyint] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[class] WITH NOCHECK ADD
CONSTRAINT [PK_class] PRIMARY KEY CLUSTERED
(
[classID]
 
ON [PRIMARY]GO
ALTER TABLE [dbo].[class] WITH NOCHECK ADD
CONSTRAINT [CK__class__capacity__6D0D32F4] CHECK ([capacity] > 0)
GO
CREATE UNIQUE INDEX [class_UNCI_classID] ON [dbo].[class]([classID]) ON [PRIMARY]
GO
CREATE UNIQUE INDEX [class_UNCI_branchID] ON [dbo].[class]([branchID]) ON [PRIMARY]
GO
-
Mark,
Thanks for the DDL. This reply may ramble a bit but I hope it is helpful.
First a clarification question. Is your intent to create a new class record every time a branch record is created? Or is it to prevent a class record from being created with a BranchID that does not yet exist?
The current configuration will create a class record via the trigger every time a new Branch record is created but will allow only one class per branch. I doubt this meets your business needs.
To allow more than one class per branch, remove the class_UNCI_branchID index or make it NON-UNIQUE.
What you have described is a Parent-Child relationship between the Branches and Class tables. The virtue of this relationship is that you need only one record for each Branch regardless of the number of classes offered there. Should the telephone or postcode change for a branch, you change one record, not many. This avoids what is known as an 'update anamoly.'
Currently you could have several records in the Branches table for a branch at 123 Oak Street, London, AB123 tel 1234567. The only difference would be the BranchID. Each record in the Branches table should uniquely represent one physical location, so there should never be more than one record with the same combination of address and phone number.
To prevent this duplication, add a unique constraint or index to the Branches table.
--Add this constraint to avoid duplicate records in the Branch table
ALTER TABLE dbo.branches
ADD CONSTRAINT UniqueBranch UNIQUE (address, tel, postcode, city)
In a Parent-Child realtionship, there should never exist a record in the class table with a BranchID that does not exist in the Branches table. This may not be absolute. If you will ever need to create a class before creating the Branch, you will not want to add this constraint. This constraint will prevent deletion of a branch record if there are any class records with the branchID. Very important in reporting.
--Add this constraint to avoid orphaned class records during updates
ALTER TABLE dbo.class
ADD CONSTRAINT FK_class_Branches FOREIGN KEY (BranchID) REFERENCES branches(branchid)
When you add a record to the class table, you will have two scenarios:
1) The Branch has had no classes assigned to it. When the Branch record was created, the trigger created a class record. You want to add the class number and capacity. You need an UPDATE command for this.
2) The branch has previously been assigned a class. You need to add a class record including the BranchID. For this you will use an INSERT command. The foreign key constraint will prevent entry of a branchID that does not correspond to a record in the Branches table.
--This statement adds a new record to the Branches table. The trigger will then be fired and create
-- a new record in the Class table as well.
INSERT branches (address, tel, postcode, city) VALUES ('123 Oak','1234567','AB123','London')
--Use this to update the class record after it has been created by the trigger on the
-- Branches table. This would only be used for the first class assigned to a new branch. --Need to know the classID assigned when the trigger created the record
UPDATE class
SET classNo = 200, capacity = 40, branchID = 2
WHERE classID = 2
--Use this to add a new class to an existing branch. This would be used for
-- 2nd, 3rd ... Nth classes for a branch. Only need to know the branchID.
INSERT class (classNo, branchid, capacity) VALUES (111, 2, 25)
If you do not need a class record created every time you create a branch, you can remove the trigger and use only the INSERT command to create class records. Without knowing your business processes, I would suppose this is the more likely case.
For someone with only three weeks' SQL experience you have entered some interesting areas of database design. Good for you. I don't have a specific book or article to recommend; however, a quick study of parent-child relationships would be helpful. Too, if you will be scheduling students and/or teachers you will want to read up on many-to-many relationships. An understanding of these concepts will simplify your database design work, ease your maintenance burdern, and help maintain data integrity.
Again, sorry for the long ramble. HTH
Chad
-
Thanks your suggestions. I've began to implement them.
The branches table will not be modified much if at all, there probably only be about 30 - 50 entries into that table. When new branch is Inserted, a new class record is created in the class table. However, there will be several class records that share the same branchID. So therefore the trigger will only be used once to create a branchID value in the class table, after that other classes that have the same branchId will have their values inserted manually through an insert or update function.
Thanks again for your help.
-
I have a requirement to update the table (on which the trigger exists) through the trigger based on Insert/Update events.
Is there any problem if I update the table on which the trigger fires.
Thanks in advance.
Manjunath
Regards,Manjunath
-
Turuvekere,
You can use a triggger to update the table on which the fired trigger is attached to.
You need to be very careful in doing this. It causes a recursive situation which can lock you up. For example, updating the FirstName field of Table1 fires a trigger which updates the same field (i.e. Table1.FirstName) which in turn fires the trigger again and so on.
Check out the Books On Line article on CREATE TRIGGER before you start writing the trigger. You should find something about RECURSIVE TRIGGERS, IF UPDATE (column), and IF (COLUMNS_UPDATED())
Good luck, and as always, this is a great place for specific help with the details.
-
Mr.Johnson,
Thanks for a lot of the quick response. Shall investigate further and take care.
Shall get back if I need any more inputs..
Regards,Manjunath
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply