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
 Database Design and Application Architecture
 How to work this trigger
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/25/2013 :  10:51:03  Show Profile  Reply with Quote
Hai,

I used this trigger. When i insert Fees table after that this trigger will executed.

But first time only execute this trigger. I dnt knw what mistake i did?

This is my trigger.

ALTER trigger [dbo].[Fees_FeeBalance_insertion]
on [dbo].[Fees] for insert
as
begin
declare @id varchar(20),@total money,@Amt money,@SFee money, @Duedate varchar(20), @fee money,@bal money,@Paydate varchar(20)
select @id=Stid,@Amt=AmtPaid,@Paydate=PaidDate,@Duedate=DueDate from inserted
select @SFee=Fees from Batch
if not exists (select SId from FeeBalance )
begin
set @bal = @SFee-@Amt
insert into FeeBalance values(@id,@Amt,@bal,@Duedate)
end
else
begin
select @fee=Total from FeeBalance
set @bal = @SFee - (@fee +@Amt)
update FeeBalance set Total=@Amt+@fee,Balance=@bal,DueDate=@Duedate where Sid=@id
end
update StudReg set UpdationDate=@PayDate where StudReg.StuId = @id
end

If any reference field is need this case?

Kindly help me.

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 10/25/2013 :  10:57:25  Show Profile  Reply with Quote
quote:
Originally posted by Arunavally

Hai,

I used this trigger. When i insert Fees table after that this trigger will executed.

But first time only execute this trigger. I dnt knw what mistake i did?

This is my trigger.

ALTER trigger [dbo].[Fees_FeeBalance_insertion]
on [dbo].[Fees] for insert
as
begin
declare @id varchar(20),@total money,@Amt money,@SFee money, @Duedate varchar(20), @fee money,@bal money,@Paydate varchar(20)
select @id=Stid,@Amt=AmtPaid,@Paydate=PaidDate,@Duedate=DueDate from inserted
select @SFee=Fees from Batch
if not exists (select SId from FeeBalance )
begin
set @bal = @SFee-@Amt
insert into FeeBalance values(@id,@Amt,@bal,@Duedate)
end
else
begin
select @fee=Total from FeeBalance
set @bal = @SFee - (@fee +@Amt)
update FeeBalance set Total=@Amt+@fee,Balance=@bal,DueDate=@Duedate where Sid=@id
end
update StudReg set UpdationDate=@PayDate where StudReg.StuId = @id
end

If any reference field is need this case?

Kindly help me.


Did you mean that it affects only one row even if your insert statement inserted multiple rows?

Assuming that is the case, the reason is that, the trigger is called only once for each insert statement, no matter how many rows are inserted via that statement.

What that means is that the INSERTED virtual table can have more than one row. So you need to write your trigger to account for that. In your code you are getting the values from INSERTED table into scalar variables. So only one out of those many rows will be used. You should rewrite it considering INSERTED to be a table with multiple rows, and joining to that.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/25/2013 :  22:56:57  Show Profile  Reply with Quote

ALTER trigger [dbo].[Fees_FeeBalance_insertion]
on [dbo].[Fees] for insert
as
begin


if not exists (select SId from FeeBalance ) 
begin
set @bal = @SFee-@Amt
insert into FeeBalance 
select Stid,AmtPaid,(select Fees from Batch)-AmtPaid,DueDate 
from inserted
end
else 
begin 
select @fee=Total from FeeBalance
set @bal = @SFee - (@fee +@Amt) 
update fb
set Total=i.Amtpaid+(select Total from FeeBalance) ,
Balance=(select Fees from Batch)-(i.Amtpaid + (select Total from FeeBalance)),
DueDate= i.Duedate 
from FeeBalance fb
join inserted i
on i.Stid = fb.Stid
where Sid=@id
end
update sr
set UpdationDate=i.PaidDate 
from StudReg sr
join inserted i
on sr.StuId = i.Stid
end


you've not specified any relationship with Batch and FeeBalance table thats why i gave subquery assuming they've a single row. otherwise you need to add them by means of join to main table using related columns

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

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/26/2013 :  08:08:26  Show Profile  Reply with Quote
Thank you.
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/26/2013 :  08:11:09  Show Profile  Reply with Quote
Hai,

How to split every letter of string in sql?

Ex1: My string is :1234
I want a=1 b=2 c=3 d=4.

Ex2: My string is :124
I want a=1 b=2 c=4 d=0

Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 10/26/2013 :  08:52:02  Show Profile  Reply with Quote
Like this:
declare @x varchar(26) = 1234;

;with N(n) as (select 1 union all select n+1 from N where n < 26)
select char(ascii('a')+n-1), SUBSTRING(CAST(@x as varchar(26)),n,1)
from N
where n <= Len(@x);
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/26/2013 :  11:41:05  Show Profile  Reply with Quote
Thank you. But i want to check
if a=1
....
..
if b=2
...
..
if c=3
..
if d=4
...
..

If it is possible? and how to write?
Kindly help me.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 10/26/2013 :  12:07:37  Show Profile  Reply with Quote
if (substring(cast(@x as varchar(26),n,1) = 1
....
..
if (substring(cast(@x as varchar(26),n,2) = 2
...
..
if (substring(cast(@x as varchar(26),n,3) = 3
..
if (substring(cast(@x as varchar(26),n,4) = 4
...
..
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/27/2013 :  02:05:34  Show Profile  Reply with Quote
Thank you.
I got error.
Invalid column name n.

If it need to declare n?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/27/2013 :  02:16:27  Show Profile  Reply with Quote
quote:
Originally posted by Arunavally

Thank you.
I got error.
Invalid column name n.

If it need to declare n?


Nope N is the column inside CTE.
See earlier post

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

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/27/2013 :  05:06:00  Show Profile  Reply with Quote
declare @x varchar(26) = 1234;

;with N(n) as (select 1 union all select n+1 from N where n < 26)
select char(ascii('a')+n-1), SUBSTRING(CAST(@x as varchar(26)),n,1)
from N
where n <= Len(@x);

if (substring(cast(@x as varchar(26),n,1) = 1
....
...

I used like this, but i got error
Invalid column name n

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/27/2013 :  14:26:08  Show Profile  Reply with Quote
you cant use like that. Tell us whats the purpose of those if statements.

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

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/28/2013 :  10:33:10  Show Profile  Reply with Quote
I solved ths issue.
I want another one help.

I want to select number of records inserted in the year 2013.

My table has CreationDate varchar(20) field. that format is 'dd-MM-yyyy'

how to select? Kindlyl help me.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 10/28/2013 :  11:11:02  Show Profile  Reply with Quote
One of these:
SELECT COUNT(*)
FROM YourTable
WHERE YEAR(CAST(@dt AS DATE)) = 2013;

SELECT COUNT(*)
FROM YourTable
WHERE @DT LIKE '%2013';
If your dateformat is not DMY, add a "SET DATEFORMAT DMY" if you want to use the first variation.
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/28/2013 :  11:35:09  Show Profile  Reply with Quote
Thank you. Its work well.

Can i join any table

SELECT COUNT(*),fb.Sum(Payables) as Exp,Sum(Receivables) as Income FROM StudReg s join Finance fb on ...... WHERE YEAR(CAST(s.CreationDate AS DATE)) = 2013;

how it is work?

I wnt to display Total no of students from student table and Total of Payables and Total of Receivables from finance table in the year 2013

It is possible? But no link between two table
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3575 Posts

Posted - 10/28/2013 :  13:07:17  Show Profile  Reply with Quote
If you don't have anything to join, you cannot join them. I would do it like shown below or some variation on that
DECLARE @year INT = 2013;

;WITH Students AS 
(
	SELECT @year AS [Year],COUNT(*) AS Students
	FROM StudentReg
	WHERE YEAR(CAST(s.CreationDate AS DATE)) = @year

),
Finance AS 
(
	SELECT @year AS [Year],SUM (Payables) AS Expenses,SUM(Receivables) AS Income
	FROM Finance
	WHERE YEAR(CAST(s.CreationDate AS DATE)) = @year -- or whatever isthe date column
)
SELECT Students, Expenses, Income
FROM Students s INNER JOIN Finance f ON f.[Year]=s.[Year];
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/28/2013 :  13:07:58  Show Profile  Reply with Quote
quote:
Originally posted by Arunavally

Thank you. Its work well.

Can i join any table

SELECT COUNT(*),fb.Sum(Payables) as Exp,Sum(Receivables) as Income FROM StudReg s join Finance fb on ...... WHERE YEAR(CAST(s.CreationDate AS DATE)) = 2013;

how it is work?

I wnt to display Total no of students from student table and Total of Payables and Total of Receivables from finance table in the year 2013

It is possible? But no link between two table



if no link it should be this

SELECT StudCnt,
Exp,
Income
FROM 
(
SELECT COUNT(*) AS StudCnt
FROM StudReg 
WHERE CreationDate > = '20130101'
AND CreationDate < '20140101'
)s
CROSS APPLY (SELECT Sum(Payables) as Exp,Sum(Receivables) as Income
 FROM Finance
 WHERE YEAR(DateField) = YEAR(s.CreationDate)
)f


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

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/29/2013 :  10:34:31  Show Profile  Reply with Quote
Thank you so much. I wl try
Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/29/2013 :  11:31:46  Show Profile  Reply with Quote
Thank you. Its work well

quote:
Originally posted by visakh16

quote:
Originally posted by Arunavally

Thank you. Its work well.

Can i join any table

SELECT COUNT(*),fb.Sum(Payables) as Exp,Sum(Receivables) as Income FROM StudReg s join Finance fb on ...... WHERE YEAR(CAST(s.CreationDate AS DATE)) = 2013;

how it is work?

I wnt to display Total no of students from student table and Total of Payables and Total of Receivables from finance table in the year 2013

It is possible? But no link between two table



if no link it should be this

SELECT StudCnt,
Exp,
Income
FROM 
(
SELECT COUNT(*) AS StudCnt
FROM StudReg 
WHERE CreationDate > = '20130101'
AND CreationDate < '20140101'
)s
CROSS APPLY (SELECT Sum(Payables) as Exp,Sum(Receivables) as Income
 FROM Finance
 WHERE YEAR(DateField) = YEAR(s.CreationDate)
)f


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


Go to Top of Page

Arunavally
Yak Posting Veteran

India
58 Posts

Posted - 10/31/2013 :  07:59:31  Show Profile  Reply with Quote
Hai, How to get last inserted record from table without id desc option.
Kindly help me.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 10/31/2013 :  08:27:55  Show Profile  Reply with Quote
quote:
Originally posted by Arunavally

Hai, How to get last inserted record from table without id desc option.
Kindly help me.


get last generated id value using SCOPE_IDENTITY and use it to retrieve last records

ie like


DECLARE @LastID int

INSERT table
.... your actual insert code

SET @LastID = SCOPE_IDENTITY()

SELECT *
FROM Table
WHERE IDCol = @LastID


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 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.12 seconds. Powered By: Snitz Forums 2000