Urgent Requirement related to Triggers and SP's

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

General SQL Server Forums

New to SQL Server Programming

Urgent Requirement related to Triggers and SP's

Author

Topic

taj
Starting Member

39 Posts

Posted - 2013-06-19 : 01:03:23

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

52326 Posts

Posted - 2013-06-19 : 01:09:13

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

taj
Starting Member

39 Posts

Posted - 2013-06-19 : 02:09:15

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 02:18:34

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

taj
Starting Member

39 Posts

Posted - 2013-06-19 : 02:43:04

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 02:50:14

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

taj
Starting Member

39 Posts

Posted - 2013-06-19 : 03:58:06

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.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 04:03:19

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

taj
Starting Member

39 Posts

Posted - 2013-06-19 : 06:00:12

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-19 : 06:04:08

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

taj
Starting Member

39 Posts

Posted - 2013-06-20 : 02:21:41

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-20 : 02:42:12

[code]
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)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources