SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 i need trigger to update and insert
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

asifbhura
Posting Yak Master

India
165 Posts

Posted - 10/14/2012 :  14:49:21  Show Profile  Reply with Quote
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

5155 Posts

Posted - 10/14/2012 :  15:07:49  Show Profile  Reply with Quote
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.
Go to Top of Page

asifbhura
Posting Yak Master

India
165 Posts

Posted - 10/14/2012 :  15:14:36  Show Profile  Reply with Quote
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,
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/14/2012 :  15:22:04  Show Profile  Reply with Quote
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).
Go to Top of Page

asifbhura
Posting Yak Master

India
165 Posts

Posted - 10/14/2012 :  15:49:35  Show Profile  Reply with Quote
st_code column
Go to Top of Page

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 10/14/2012 :  16:03:50  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000