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
 General SQL Server Forums
 New to SQL Server Programming
 Urgent Requirement related to Triggers and SP's
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

taj
Starting Member

India
39 Posts

Posted - 06/19/2013 :  01:03:23  Show Profile  Reply with Quote
Hi all,

Requirement:

1)Table Name: Emp
Column Names: EmpID int,Ename varchar(20),Deptid int,Locid int
Note: EmpID is primary key (not identity)
DeptID is foreign Key with Dept table
LocID is foreign key with Loc table

2)Table Name: Dept
Column Names: Deptid int,Dname varchar(20)
Note: Deptid is primary key (not identity)

3)Table Name: Loc
Column Name: Locid int,Lname varchar(20)

Now i will create a view to get the full employee details from all above mentioned tables.

create view vwemployee
as
select E.Empid,E.Ename,D.Dname,L.Lname
from Emp E
inner join Dept D
on E.DeptID=D.DeptID
inner join Loc L
on E.LocID=L.LocID

Now when i execute above created view my Result Will be EmpID,Ename,Dname,Lname
so this same result as it is i want to insert into one more table mentioned below

4) Table Name: Employees
Column Names:
Empid int,Ename varchar(20),Dname varchar(20),Lname varchar(20)

IS IT POSSIBLE TO WRITE A TRIGGER ON VIEW,IF IT IS THEN WHICH TRIGGER I HAVE TO WRITE TO PERFORM FOR ABOVE REQUIREMENT?
OR
IS IT POSSIBLE TO WRITE A SINGLE STORED PROCEDURE FOR BOTH SELECTING DATA FROM MULTIPLE TABLES AS MENTIONED ABOVE AND THEN INSERTING THE SAME INTO MY 4th TABLE (EMPLOYEES)?

solutions with queries for the above requirement will be appreciated.

Thank you


visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/19/2013 :  01:09:13  Show Profile  Reply with Quote
its possible to write a trigger on view but i didnt understand purpose for that. For your requirement what you need is just a batch sql statement or a procedure where you can do the inserting of data from three table to fourth table

it will be like

CREATE PROCEDURE InsertEMployees
AS
INSERT INTO Employees
select E.Empid,E.Ename,D.Dname,L.Lname
from Emp E
inner join Dept D
on E.DeptID=D.DeptID
inner join Loc L
on E.LocID=L.LocID
WHERE NOT EXISTS (SELECT 1 FROM EMployees
                  WHERE Empid = E.Empid)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

taj
Starting Member

India
39 Posts

Posted - 06/19/2013 :  02:09:15  Show Profile  Reply with Quote
thanks for your quick reply....

i will surely try with your suggested solution....

Actually my requirement is, i have to write a single SP which does select and insert operation, I have 4 to 5 tables which i have to join and then insert the same into one table.

NOTE:As there is some entry into select table,then the same should be automatically inserted into the insert table.

For this requirement i thought of creating a view and then creating a trigger on the view which insert the data from view into my resultant table.

If you don't mind can you give me the query for creating the trigger on view which can perform my requirement.

IS IT POSSIBLE FOR TO WRITE A SINGLE TRIGGER ON MULTIPLE TABLES.AS THERE IS SOME ENTRY INTO MULTIPLE TABLES,THEN IT SHOULD INSERT THE SAME INTO ONE TABLE?

Thank you.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/19/2013 :  02:18:34  Show Profile  Reply with Quote
no need of trigger again. My posted suggestion does the select and insert in the same step which is what you want as per your explanation given below


i have to write a single SP which does select and insert operation, I have 4 to 5 tables which i have to join and then insert the same into one table.


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

taj
Starting Member

India
39 Posts

Posted - 06/19/2013 :  02:43:04  Show Profile  Reply with Quote
I have tried this as you suggested,but i'm unable to fix it...please have look and help me out in fixing this.
create table memp
(empid int,ename varchar(20),deptid int,locid int)
create table mdept
(deptid int,dname varchar(20))
create table mloc
(locid int,lname varchar(20))
create table memployees
(empid int,ename varchar(20),dname varchar(20),lname varchar(20))

SELECT * FROM memp
1 SHAIK 1 1
2 ABHI 2 2
3 MAHESH 3 3
4 SIRAJ 4 4
5 RAVI 5 5
6 KUMAR 6 6
SELECT * FROM mdept
1 IT
2 HR
3 PAYROLL
4 FINANCE
5 TESTING
6 R&D
7 ADMIN
SELECT * FROM mloc
1 INDIA
2 USA
3 UK
4 UAE
5 AFRICA
6 PAKISTAN
7 AP

Create PROCEDURE InsertEMployees
AS
INSERT INTO memployees
select E.Empid,E.Ename,D.Dname,L.Lname
from memp E
inner join mdept D
on E.DeptID=D.DeptID
inner join mloc L
on E.LocID=L.LocID
WHERE NOT EXISTS (SELECT * FROM memployees
WHERE empid = E.Empid)

INSERT INTO memp VALUES(7,'RAJ',7,7)

select * from memployees

still my memployees table is empty, it has to show me the previous and latest inserted records.

Thank you.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/19/2013 :  02:50:14  Show Profile  Reply with Quote

it has to show me the previous and latest inserted records.

where did this part come from? You didnt specify it yet.

also in your above code you've just created procedure, you didnt execute it


Create PROCEDURE InsertEMployees
AS
INSERT INTO memployees
select E.Empid,E.Ename,D.Dname,L.Lname
from memp E
inner join mdept D
on E.DeptID=D.DeptID
inner join mloc L
on E.LocID=L.LocID
WHERE NOT EXISTS (SELECT * FROM memployees 
WHERE empid = E.Empid)

Go

--now execute it as

EXEC InsertEMployees

Go

-- now check the result 
select * from memployees

--new value
INSERT INTO memp VALUES(7,'RAJ',7,7)

--now execute it do the insert

EXEC InsertEMployees

Go

-- noe check result again
select * from memployees






------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

taj
Starting Member

India
39 Posts

Posted - 06/19/2013 :  03:58:06  Show Profile  Reply with Quote
oh sorry for not executing the stored procedure...thanks a lot once again for your help....

i need one more small help from you.....
SELECT * FROM MEMP
EmpID Ename DeptID LocID
1 SHAIK 1 1
2 ABHI 2 2
3 MAHESH 3 3
4 SIRAJ 4 4
5 RAVI 5 5
6 KUMAR 6 6
8 RAJU 5 4
9 RAJU 5 4

suppose if i have more columns compared to above memp table in my memployee table
Select * from memployees
EmpID Ename DeptID LocID Pname
1 SHAIK IT INDIA NULL
2 ABHI HR USA NULL
3 MAHESH PAYROLL UK NULL
4 SIRAJ FINANCE UAE NULL
5 RAVI TESTING AFRICA NULL
6 KUMAR R&D PAKISTANNULL
8 RAJU TESTING UAE NULL
9 RAJU TESTING UAE NULL

the previously created stored procedure will give an error saying that Msg 213, Level 16, State 1, Procedure InsertEMployees, Line 3
Column name or number of supplied values does not match table definition.

Because No of columns in both the tables did not match.

Now i want to alter my previously created stored procedure in such a way that if No. of columns did not match....then the non-matching columns should be stored with null value...to do this what condition i have to pass in my stored procedure.

Thank you.

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/19/2013 :  04:03:19  Show Profile  Reply with Quote

Create PROCEDURE InsertEMployees
AS
INSERT INTO memployees
(EmpID, Ename, DeptID, LocID, Pname)
select E.Empid,E.Ename,D.Dname,L.Lname,NULL
from memp E
inner join mdept D
on E.DeptID=D.DeptID
inner join mloc L
on E.LocID=L.LocID
WHERE NOT EXISTS (SELECT * FROM memployees 
WHERE empid = E.Empid)

Go


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

taj
Starting Member

India
39 Posts

Posted - 06/19/2013 :  06:00:12  Show Profile  Reply with Quote
Thanks much for your help.....

if i have not null columns ex: salary in my memployee table, then what condition i have to pass in my stored procedure.

IS IT POSSIBLE TO INSERT EMPTY SPACE IN NOT NULL COLUMNS?

Thank you
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/19/2013 :  06:04:08  Show Profile  Reply with Quote
quote:
Originally posted by taj

Thanks much for your help.....

if i have not null columns ex: salary in my memployee table, then what condition i have to pass in my stored procedure.

IS IT POSSIBLE TO INSERT EMPTY SPACE IN NOT NULL COLUMNS?

Thank you



its possible as empty space ('') is not same as NULL
NULL represents a condition of unknown value and its not actually stored as a value whereas '' is a value by itself

in your case, since it is a Salary field you should be using a numeric value as default (0,-1 etc depending on your business definition)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

taj
Starting Member

India
39 Posts

Posted - 06/20/2013 :  02:21:41  Show Profile  Reply with Quote
thanks much for your last reply......

CREATE PROCEDURE InsertEMployees
AS
INSERT INTO mEmployees(Empid,Ename,dname,lname)
select E.Empid,E.Ename,D.Dname,L.Lname
from mEmp E
inner join mDept D
on E.DeptID=D.DeptID
inner join mLoc L
on E.LocID=L.LocID
WHERE NOT EXISTS (SELECT Empid,Ename,dname,lname FROM EMployees
WHERE Empid = E.Empid)

From the above insert query in my insert table i have one more column sampleid bigint(notnull),this sampleid column is not available in any other tables(memp,mdept,mloc) so that i can join it and get the sampleid. so as of now i want to show the sampleid as 0(zero)in my resultant table(memployees).so want changes i have to make in my above stored procedure code. Plz suggest me.....

Thank You
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 06/20/2013 :  02:42:12  Show Profile  Reply with Quote

CREATE PROCEDURE InsertEMployees
AS
INSERT INTO mEmployees(Empid,Ename,dname,lname,sampleid)
select E.Empid,E.Ename,D.Dname,L.Lname,0
from mEmp E
inner join mDept D
on E.DeptID=D.DeptID
inner join mLoc L
on E.LocID=L.LocID
WHERE NOT EXISTS (SELECT Empid,Ename,dname,lname FROM EMployees
WHERE Empid = E.Empid)


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
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