


CREATE TRIGGER [dbo].[STOKMIADBAKIYE_HASTA_BAKIYEKONTROL_TRG] ON [dbo].[STOKMIADBAKIYE] 
FOR INSERT, UPDATE
AS
SET NOCOUNT ON
DECLARE
   @STOKMIADBAKIYEID int, 
   @SUBEID int,
   @DEPOID int,
   @STOKID int, 
   @SKTARIHI datetime,
   @MIKTAR float,
   @MiadZorunlu bit,
   @ESKIMIKTAR float,
   @MIKTARFARK float,
   @STOKADI nvarchar(100),
   @DEPOADI nvarchar(100),
   @SKTARIHISTR nvarchar(50),
   @HASTAMIKTARFARK float,
   @HASTAMIKTAR float,
   @ESKIHASTAMIKTAR float
   --MIKTAR - case when HASTASIZMIKTAR>0 then HASTASIZMIKTAR else 0 end AS HASTAMIKTAR

DECLARE @UPDATED_CURSOR CURSOR 

SET @UPDATED_CURSOR = CURSOR FAST_FORWARD FOR
SELECT I.STOKMIADBAKIYEID, I.SUBEID, I.DEPOID, 
       I.STOKID, I.SKTARIHI, I.MIKTAR, STOKGRUP.MIADZORUNLU, D.MIKTAR, 
	   I.MIKTAR - case when I.HASTASIZMIKTAR>0 then I.HASTASIZMIKTAR else 0 end AS IHASTAMIKTAR  ,
	   D.MIKTAR - case when D.HASTASIZMIKTAR>0 then D.HASTASIZMIKTAR else 0 end AS DHASTAMIKTAR
FROM INSERTED I full outer join 
     DELETED D ON I.STOKMIADBAKIYEID = D.STOKMIADBAKIYEID LEFT JOIN STOK (NOLOCK) ON 
I.STOKID = STOK.STOKID LEFT JOIN STOKGRUP (NOLOCK) ON 
STOK.GRUP = STOKGRUP.KODU

OPEN @UPDATED_CURSOR
FETCH NEXT FROM @UPDATED_CURSOR 
INTO @STOKMIADBAKIYEID, @SUBEID, @DEPOID, 
     @STOKID, @SKTARIHI, @MIKTAR, @MiadZorunlu, @ESKIMIKTAR, @HASTAMIKTAR,   @ESKIHASTAMIKTAR
     
WHILE @@FETCH_STATUS = 0
BEGIN
  IF (@STOKMIADBAKIYEID IS NOT NULL)
  BEGIN
	-- Ekleme, Güncelleme
		
	SET @HASTAMIKTARFARK = @HASTAMIKTAR - ISNULL(@ESKIHASTAMIKTAR, 0)	
	IF @HASTAMIKTAR < -1E-14 AND ISNULL(@ESKIHASTAMIKTAR, 0) < 0
	BEGIN
		IF (ISNULL(@MiadZorunlu,0)=1)
		BEGIN
			IF EXISTS(SELECT TOP 1 BIRIMID FROM TBIRIM WHERE TBIRIM.DEPOID = @DEPOID AND MIADBAKIYEKONTROL = 1)
			BEGIN
				SELECT @STOKADI = ADI FROM STOK WHERE STOKID = @STOKID
				SELECT @DEPOADI = DEPOADI FROM DEPO WHERE DEPOID = @DEPOID
				SET @SKTARIHISTR = cast(@SKTARIHI as nvarchar(50))
				RAISERROR ('%s stoğunun %s deposundaki, %s son kullanma tarihi HASTA bakiyesi negatif olamaz!', 16, 1, @STOKADI, @DEPOADI, @SKTARIHISTR)
				ROLLBACK TRANSACTION
				BREAK
			END
			END
	 		 
		ELSE
		BEGIN
			IF EXISTS(SELECT TOP 1 DEPO.DEPOID FROM DEPO WHERE DEPO.DEPOID = @DEPOID AND DEPO.MIKTARKONTROL = 1)
			BEGIN
				SELECT @STOKADI = ADI FROM STOK WHERE STOKID = @STOKID
				SELECT @DEPOADI = DEPOADI FROM DEPO WHERE DEPOID = @DEPOID
				RAISERROR ('%s stoğunun %s deposundaki HASTA bakiyesi negatif olamaz!', 16, 1, @STOKADI, @DEPOADI)
				ROLLBACK TRANSACTION
				BREAK
			END
		END
	END

	
	--XXX
  END   

  FETCH NEXT FROM @UPDATED_CURSOR 
  INTO @STOKMIADBAKIYEID, @SUBEID, @DEPOID, 
     @STOKID, @SKTARIHI, @MIKTAR, @MiadZorunlu, @ESKIMIKTAR, @HASTAMIKTAR,   @ESKIHASTAMIKTAR
END

CLOSE @UPDATED_CURSOR
DEALLOCATE @UPDATED_CURSOR


GO

ALTER TABLE [dbo].[STOKMIADBAKIYE] ENABLE TRIGGER [STOKMIADBAKIYE_HASTA_BAKIYEKONTROL_TRG]
GO


