Database

CREATE PROCEDURE Foo
   @FooColumn int = NULL
   @ProcessStatus varchar(100) = NULL
AS
   // your insert query
   IF @@ERROR <>0
   BEGIN
      ROLLBACK
      SET @ProcessStatus = 'Rollbacked'
   END
   ELSE
   BEGIN
      SET @ProcessStatus = 'Completed'
   END

CREATE TRIGGER sp_Kp1 ON [dbo].[OEORDD] 
for insert, update
AS
BEGIN
  INSERT INTO table2 (tx,ty,tz) VALUES @x,@y,@z)
  SELECT i.xx, 
         i.ii, 
         t.zz
  FROM   inserted i,
         table1   t
  WHERE  t.xx = i.xx
  AND    t.yy = i.yy
END;

monuSaini wrote:

This trigger is working fine only if one row is updated
but it fails if i update more than one row using update query.

CodingYoshi wrote:

Table1 has a identity column of type int which is auto generated so I am not inserting nothing into it.

CodingYoshi wrote:

Table1 has a identity column of type int which is auto generated so I am not inserting nothing into it

INSERT INTO 
dbo].[Table1]
(
   [col1]
   , [col2]
   , [AccountCode]
   , [col4]
   , [col5]
   , [colDate]
)
SELECT 
   [col1]
   , AccountCode
   , '12345' 
   , '12345'
   , 100
   , '01/16/2009'
FROM #TempTable

SET IDENTITY_INSERT [Table1] ON
INSERT...
SET IDENTITY_INSERT [Table1] OFF

Unsy wrote:

Is there a way to catch the division by zero error and insert a '0' without the code throwing an exception

SELECT 
    Field1
   ,Field2
   ,SUM(
        SUM(CASE WHEN Field3 IS NOT NULL OR Term - @WeeksPassed > 0 THEN 0 ELSE 1 END))
        /
        SUM(CASE WHEN Field4 IS NOT NULL OR Term - @WeeksPassed > 0 THEN 0 ELSE 1 TotalPaid END))
       )
FROM
    Table1
GROUP BY 
    Field1
   ,Field2

Unsy wrote:

Please excuse the SQL code if it is full of errors as I am 250 miles from home

SELECT
    Field1
   ,Field2
   ,SUM( CASE Divisor
            WHEN 0 THEN 0
            ELSE Amount / Divisor
         END)
FROM (
   SELECT Field1,
          Field2,
          SUM(CASE 
                 WHEN Field3 IS NOT NULL OR Term - @WeeksPassed > 0 
                 THEN 0 
                 ELSE 1 
              END
          ) amount,
          SUM(CASE 
                 WHEN Field4 IS NOT NULL OR Term - @WeeksPassed > 0 
                 THEN 0 
                 ELSE 1 
              END
          ) divisor
   FROM
       Table1
   GROUP BY
       Field1
      ,Field2) alias
GROUP BY
    Field1
   ,Field2