i need trigger to update and insert

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

i need trigger to update and insert

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2012-10-14 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-14 : 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

165 Posts

Posted - 2012-10-14 : 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
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-14 : 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

165 Posts

Posted - 2012-10-14 : 15:49:35

st_code column

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-14 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources