| Author |
 Topic  |
|
|
asifbhura
Posting Yak Master
India
157 Posts |
 Posted - 10/14/2012 : 14:49:21
|
Hello,
I have two tables: StudentInfo, ApproveResult
both tables have almost same structure,
I want that, whenever records inserted or updated in studentinfo
the approveresult must be updated / inserted, but it must check whether record is existed, if existed then it should not insert...
Regards.
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/14/2012 : 15:07:49
|
In your trigger, you would need to join the INSERTED table with the ApproveResult table and update or insert as appropriate. That of course means that you will need a primary key (or other criteria) to join the two tables.
Don't you also need to delete any rows that may have been deleted from the StudentInfo table?
If you can post the table schemas along with the code that you have written (even if it does not work exactly as you want it) people on the forum would be able to offer suggestions/corrections. |
 |
|
|
asifbhura
Posting Yak Master
India
157 Posts |
Posted - 10/14/2012 : 15:14:36
|
can you please show me how to do so,
here is the schema
studentinfo table
[SrNo] [bigint] IDENTITY(1,1) NOT NULL,
[Grade] [nvarchar](255) NOT NULL,
[St_Code] [nvarchar](255) NULL,
[StName] [nvarchar](255) NULL,
[Mobile] [bigint] NULL,
[Section] [nvarchar](255) NULL,
[Nationality] [nvarchar](255) NULL,
ResultApprove table
[Srno] [bigint] IDENTITY(1,1) NOT NULL,
[St_code] [nvarchar](50) NULL,
[St_Name] [nvarchar](100) NULL,
[Grade] [nvarchar](255) NULL,
[IsShow] [bit] NULL, |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/14/2012 : 15:22:04
|
What are the columns in the two table that can be used to relate a given row in one table to a row in the other table? In other words, if you insert or update data for a Student into the StudentInfo table, what columns in the ApproveResult table can tell us whether or not a row for that student exists in the ApproveResult table?
I would have thought that it is SrNo, but that couldn't be because the columns have IDENTITY property in both tables. Identity columns are sort of like wild horses - they can run around on their own and not really follow orders from another (column in another table). |
|
|
|
asifbhura
Posting Yak Master
India
157 Posts |
Posted - 10/14/2012 : 15:49:35
|
| st_code column |
|
|
|
sunitabeck
Flowing Fount of Yak Knowledge
5152 Posts |
Posted - 10/14/2012 : 16:03:50
|
You would do something like shown below. But I don't see many common columns between the two tables, so I included only the common columns. I wrote it as two triggers - you could conceivably combine them into a single trigger, but I think this is simpler:CREATE TRIGGER dbo.StudentInfoUpdateTrigger ON StudentInfo
FOR UPDATE
AS
UPDATE R SET
St_Name = i.StName,
Grade = i.Grade
FROM
INSERTED i
INNER JOIN ResultApprove r ON
r.st_code = i.st_code;
GO
CREATE TRIGGER dbo.StudentInfoInsertTrigger ON StudentInfo
FOR INSERT
AS
INSERT INTO ResultApprove
(st_code, Grade)
SELECT
st_code,
Grade
FROM
INSERTED;
GO |
|
|
| |
Topic |
|
i need trigger to update and insert
